Displaying the Database details

July 14, 2012

The below syntax will display the details of a particular database or all databases:

SP_HELPDB ‘<Database Name>’

The above query will display the details of a particular database. The below query will display the details of all the databases:

SP_HELPDB


How to bring the database to Multi-User mode?

July 13, 2012

The below set of queries would bring the database to Multi-User mode:

USE master;
ALTER DATABASE <Database Name> SET MULTI_USER;
GO


How to bring the database to a Single User mode?

July 13, 2012

The below set of queries would bring the database to a single user mode:

USE master;
ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ROLLBACK IMMEDIATE will bring the database to the single user mode immediately after rolling back the transactions.

 


Query to display the size of a table/database in SQL Server

July 12, 2012

The following query will determine the size of a particular table you want to know in a database:

Use <Database Name>

sp_spaceused ‘<Table Name>’

If we use the following queries, the whole database size will be displayed:

Use <Database Name>

sp_spaceused

 


How to Detach a Database?

July 11, 2012

If you want to move a database from one server to another. This would be a quick method to do it.  I have given below the Query to detach a database:

USE master
GO
EXEC sp_detach_db ‘<Database Name>’


How to Enable/Disable all the constraints in a Database?

July 10, 2012

Here are the queries to enable or disable all the constraints in a Database:

How to disable all the constraints in a Database?

  • Use <Database Name>
  • EXEC sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

How to enable all the constraints in a Database?

  • Use <Database Name>
  • EXEC sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

 

 


%d bloggers like this: