Home > Tips N Tricks > Database Summary

Database Summary

October 6th, 2009 Arun Mallick Leave a comment Go to comments


Problem: View sample data for each table in a database.

Solution: Many times we may come across some situations especially when looking for any information on a big database or when exploring tables on it, where we may feel to get some easy methods to get the whole thing instead of browsing each table one by one either by T-SQL statements or by UI of SSMS.

So, here is a tweak which gives you the whole picture of the database with relevant information i.e. Table Name, Total No. of Records and some sample records (usually top 5).


The below procedure uses sysobjects and sysindexes to get the information that we want: 

-- =============================================
-- Author:       <Arun Kumar Mallick>
-- Description: <List all table names, Total Row count for each table and sample data from each table in a given database>
-- =============================================
/*
 
EXEC dbo.[DatabaseSummary]
 
*/
ALTER PROCEDURE [dbo].[DatabaseSummary]
	AS
BEGIN
	SET NOCOUNT ON;
 
    DECLARE @tablename VARCHAR(200)
	DECLARE @totalRowCount INT
	DECLARE @SQLstr NVARCHAR(500)
 
	DECLARE tabSumCur CURSOR
	FOR
	SELECT sysobjects.name, rowcnt FROM sysindexes INNER join sysobjects
	ON sysindexes.id = sysobjects.id and sysobjects.xtype = 'U' and (indid = 0 or indid = 1) ORDER BY sysobjects.name
 
	OPEN tabSumCur
	FETCH NEXT FROM tabSumCur INTO @tablename, @totalRowCount
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--SET @SQLstr = 'select top 4 ''' + @tablename +''' as TableName, * from ' + @tablename
		SELECT @tablename AS TableName, @totalRowCount AS TotalRowCount
		SET @SQLstr = 'select top 4 * from ' + @tablename
		EXEC SP_EXECUTESQL @SQLStr
 
		FETCH NEXT FROM tabSumCur INTO @tablename, @totalRowCount
	END
 
	CLOSE tabSumCur
	DEALLOCATE tabSumCur
 
END


Categories: Tips N Tricks
  1. No comments yet.
  1. No trackbacks yet.