.gif)
you can rename a table in SQL Server 2005,2008 and 2012 by using SQL Server Management Studio or Transact-SQL.
Renaming a table will not automatically rename references to that table. You must manually modify any objects that reference the renamed table. For example, if you rename a table and that table is referenced in a trigger, you must modify the trigger to reflect the new table name.
Here i am describe all the ways to rename a table databse and column.
1-Rename Database:
Renaming a table will not automatically rename references to that table. You must manually modify any objects that reference the renamed table. For example, if you rename a table and that table is referenced in a trigger, you must modify the trigger to reflect the new table name.
Here i am describe all the ways to rename a table databse and column.
1-Rename Database:
1-sp_renameDB 'oldDB','newDB'
2-ALTER DATABASE Test
MODIFY NAME =
NewTest
2-Rename column name
Now, we see how to
change the Column Name the below script will change table column name from “OldName”
to “NewNameChange”
USE yourdatabasename
GO
sp_RENAME 'TableName.OldName', 'NewNameChange' , 'COLUMN'
GO:
GO
sp_RENAME 'TableName.OldName', 'NewNameChange' , 'COLUMN'
GO:
3.Renaming database table to new name.
We can also change the table name too with using the same
command.
sp_RENAME 'oldname', 'newname'GO
To rename a table
- In Object Explorer, right-click the table you want to
rename and choose Design from the shortcut menu.
- From the View menu,
choose Properties.
- In the field for the Name value
in the Properties window, type a new name for the table.
- To cancel this action, press the ESC key before leaving
this field.
- From the File menu
choose Save table
name.
you can rename a database in SQL Server 2005,2008 and 2012 by using SQL Server Management Studio or Transact-SQL.
Here i am describe all the ways to rename a Database .
Rename Database using Transact-SQL.:
Using SQL Server Management Studio To rename a Database:
1-In Object Explorer, right-click the database you want to rename and choose Design from the shortcut menu.
2-From the View menu, choose Properties.
3-In the field for the Name value in the Properties window, type a new name for the database.
4-To cancel this action, press the ESC key before leaving this field.
5-From the File menu choose Save name.
Here i am describe all the ways to rename a Database .
Rename Database using Transact-SQL.:
1-sp_renameDB 'oldDB','newDB'
2-ALTER DATABASE Test
MODIFY NAME =
NewTest
Using SQL Server Management Studio To rename a Database:
1-In Object Explorer, right-click the database you want to rename and choose Design from the shortcut menu.
2-From the View menu, choose Properties.
3-In the field for the Name value in the Properties window, type a new name for the database.
4-To cancel this action, press the ESC key before leaving this field.
5-From the File menu choose Save name.
This topic is about sql server Comma Separated Values(csv) from Table Column.
There are Many times where we need to convert a set of rows in a single column to a comma separated (CSV) string , in this article i am going to tell you how you can get csv values from sql server table column.
For this sql server query example i have created a Temporary Table in Sql Server Database.
CREATE TABLE #MyTable(Column1 NVARCHAR(10))
INSERT INTO #MyTable VALUES('sdf')INSERT INTO #MyTable VALUES('dfg')INSERT INTO #MyTable VALUES('ghj')INSERT INTO #MyTable VALUES('rty')INSERT INTO #MyTable VALUES('hjk')
Here i am showing Two methods to create Comma Separated Values(csv) from Table Column.
1-Create Comma Seperated Values(CSV) Using COALESCE Method
DECLARE @MakeStr VARCHAR(200)
SELECT @MakeStr = COALESCE(@MakeStr+',' ,'') + Column1
FROM #MyTable
SELECT @MakeStr as MyCSV
2-Create Comma Seperated String Using XML Method
DECLARE @MakeStr VARCHAR(200)
SELECT SUBSTRING(
(SELECT ',' + Column1
FROM #MyTable
ORDER BY Column1
FOR XML PATH('')),2,200) AS MyCSV
And the result for the above Sql Query Will be
MyCSV
——————–
sdf,dfg,ghj,rty,hjk
MyCSV
——————–
dfg,ghj,hjk,rty,sdf
And the result for the above Sql Query Will be
MyCSV
——————–
sdf,dfg,ghj,rty,hjk
MyCSV
——————–
dfg,ghj,hjk,rty,sdf
For Moving User Database Files to the New Location On the Same Server in MS SQL Server You Can use Database backup and restore which is the best method . But, when you need to move databases to the new location on the same server, backup and restore creates too much overhead. The below Steps will do the same job with as little downtime as possible.
-- Step1 the
below query will make database offline and break all active connections
alter database YOURDATABASENAME
set offline
with rollback immediate
go
-- Step 2 the
below query will detach database
use master
go
sp_detach_db 'YOURDATABASENAME'
go
-- Step 3 the
below query will Show the current data
and log file locations
use
YOURDATABASENAME
go
sp_helpfile
go
-- Step 4 copy
data and log files to the new location
-- Step 5 the
below query will reattach database Confirm you specify correct paths to .mdf and .ldf files)
use master
go
sp_attach_db 'YOURDATABASENAME','D:\SqlData\YOURDATABASENAME.mdf','D:\SqlLogs\
YOURDATABASENAME_log.ldf'
YOURDATABASENAME_log.ldf'
go
-- Step 6 verify
that the database is using the new files location
use
YOURDATABASENAME
go
sp_helpfile
