sql server 2012

Whats the Difference Between Detaching And Taking Database offline?

Posted on Updated on

Once you detach your database there will not be an entry in the sysdatabases system table.
i.e you can query and confirm the same as

 Select * from master..sysdatabases

In the end result of the above query you cannot see the db you have detached whereas
if you bring the database offline, there will be an entry present in the sysdatabases system table;
You can verify the same by running the above query and you can see that the db you took OFFLINE will be present in the sysdatabases table !

Basically, detaching/attaching is used to move database files between instances(servers) where as in Offline you can not move files. After detaching, the database will be removed from SQL Server but will be intact within the data and transaction log files that compose the database. The purpose of these commands are totally different though it looks like same.

Rename Table In SQL SERVER

Posted on Updated on

We can rename table by using T-SQL or SQL server management Studio.

Before renaming any object in SQL server we have to check the dependency of that object.

We can check it by using sp_depends  Or sys.sql_expression_dependencies this DMV.

After renaming table we have to change the table name in each queries, stored procedure, views, function and all other objects where it was being referenced.

Using SQL Server Management Studio

To rename a table

  1. In Object Explorer, right-click the table 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 table.
  4. To cancel this action, press the ESC key before leaving this field.
  5. From the File menu choose Save table name.

 or Simply select the object and press F2 key and type the new name.

Using T SQL:

USE YourDatabaseName;

GO

EXEC sp_rename ‘Schema_name.old_Table_name’, ‘new_Table_name ‘;