DB Object Report

				
--**************************************
-- Name: DB Object Report
-- Description:Script to report on number of foreign keys, primary keys, indexes, triggers, views, tables (user and system), stored procedures, defaults and rules. Great for comparing two or more DB's against a known master copy, etc.
-- By: MisterD
--
-- Returns:Object counts of each type, as well as the total object count at the bottom of the report.
--
-- Assumes:Just fire it on each DB you want to compare results from, that's it!
--
-- Side Effects:More free time, due to not having to manually guess at the schema differences between two or more DB's anymore! :)
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=316&lngWId=5--for details.--**************************************

SET NOCOUNT ON
DECLARE	@Output		char( 255 ),
	@Total		int,
	@Count		int
PRINT	'====================================================================='
SELECT	@Output = ' Object Check on ' + RTRIM( DB_Name() )
	+ ' as of ' + RTRIM( CONVERT( char( 19 ), GetDate() ) )
PRINT	@Output
PRINT	'====================================================================='
PRINT	''
SELECT	@Total	= 0,
	@Count	= 0
-- Count Stored Procedures
SELECT	@Count = COUNT(*)
FROM	SysObjects
WHERE	XType = 'P'
SELECT	@Output = 'Stored Procedures : '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
-- Count System tables
SELECT	@Count = COUNT(*)
FROM	SysObjects
WHERE	XType = 'S'
SELECT	@Output = 'System Tables : '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
-- Count Foreign keys
SELECT	@Count = COUNT(*)
FROM	SysObjects
WHERE	XType = 'F'
SELECT	@Output = 'Foreign Keys : '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
-- Count Primary keys
SELECT	@Count = COUNT(*)
FROM	SysObjects
WHERE	XType = 'PK'
SELECT	@Output = 'Primary Keys : '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
-- Count User tables
SELECT	@Count = COUNT(*)
FROM	SysObjects
WHERE	XType = 'U'
SELECT	@Output = 'User Tables: '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
-- Count Triggers
SELECT	@Count = COUNT(*)
FROM	SysObjects
WHERE	XType = 'TR'
SELECT	@Output = 'Triggers : '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
-- Count Defaults
SELECT	@Count = COUNT(*)
FROM	SysObjects
WHERE	XType = 'D'
SELECT	@Output = 'Default: '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
-- Count Indexes
SELECT	@Count = COUNT(*)
FROM	SysIndexes
WHERE	IndID > 0 
AND	IndID < 255
AND	( Status & 64 )= 0
AND	( Status & 8388608 ) = 0
AND	( Status & 16777216 ) = 0
SELECT	@Output = 'Indexes: '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
-- Count Rules
SELECT	@Count = COUNT(*)
FROM	SysObjects
WHERE	XType = 'R'
SELECT	@Output = 'Rules : '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
-- Count Views
SELECT	@Count = COUNT(*)
FROM	SysObjects
WHERE	XType = 'V'
SELECT	@Output = 'Views : '
	+ RTRIM( CONVERT( char( 12 ), @Count ) )
PRINT	@Output
SELECT	@Total = @Total + @Count
PRINT	''
PRINT	'====================================================================='
SELECT	@Output = 'Total Objects : '
	+ RTRIM( CONVERT( char( 12 ), @Total ) )
PRINT	@Output
PRINT	'====================================================================='
SET NOCOUNT OFF