.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.
Here is a SQL Query to find the list of Functions in SQL Server database.When you run this query it will show the list of all functions in SQL Server DataBase.
SELECT name FROM sys.objects
WHERE type
IN ('AF ','FN', 'IF', 'TF', 'FS', 'FT')
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
