Irfan's World

November 1, 2011

How to use undocumented functions of SQL Server ?

Here in this post, I am going to explain some undocumented functions of SQL Server.  These functions are very useful to get the information about a database or a table quickly as a part of maintenance and other daily tasks.  I regularly use sp_MSforeachtable to count the rows of the tables in our data warehouse and the source systems.  I also use sp_MSforeachDB  to get a count of objects in a database when a production database is refreshed on to a UAT or DEV environments.

  To get a count of objects for all the databases on a Host

sp_msforeachdb ‘select ”?” as DatabaseName, count(*) as CountObjects from ?.sys.objects’

  To get a count of rows from all the tables in a database

sp_msforeachtable ‘select ”?”, Count(*) from ?’


Note:  There are single quotes around the question mark(?) in the above syntax.

October 12, 2011

Drop Foreign Key Constraints in SQL Server 2008

Filed under: SQL Server 2000/2005/2008 — Tags: , , — Irfan @ 4:54 pm

The following SQL generates ALTER TABLE DROP CONSTRAINT sql for each table in the Database.  This script is used mostly when you try to TRUNCATE the tables in a database.

 SELECT ‘ALTER TABLE ‘ +
       ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ +
       ‘ DROP CONSTRAINT [‘ + f.name +’]’
FROM sys.foreign_keys f
INNER JOIN sys.tables t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0;

The ALTER TABLE sql statements that it has generated on my database is shown below. I made the image little blurred on purpose.

image

Don’t forget to press Ctrl+T to generate the sql in Text format in SSMS.

Blog at WordPress.com.