Justin Cooney

When you are working with an unfamiliar database, such as a third-party SQL Server database, you want to find out which tables actually store the important data. If there is no clean database diagram, then the best option is to write a T-SQL query in SQL Server Management Studio (SSMS).

If you are interested in this topic, then I’d suggest also reading my article on batch searching your SQL Server database to find out in which tables the information is being stored.

The Logic Behind the Code

To differentiate supporting tables from data storage tables, we can simply assume that tables with more than 50 rows of data are storage tables that we are interested in. To get this data and report on it, we can loop through the database table names in sys.tables, and then get a row count for each table within a sub-query, that can then be saved for a final query output to the user.

The Basic Working Example

Below is the T-SQL that will return the row count and table name for each table in a database that has more than 50 rows:

DECLARE @TableName VARCHAR(255)
DECLARE @ResultsTable TABLE(
 RowCnt INT,
 TableName VARCHAR(500)
 )
DECLARE MY_CURSOR Cursor
FOR
SELECT name FROM sys.tables
ORDER BY name
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @ResultsTable (RowCnt,TableName)
Exec('SELECT COUNT(*),''' + @TableName + ''' FROM ' + @TableName)
FETCH NEXT FROM MY_CURSOR INTO @TableName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
SELECT RowCnt,TableName FROM @ResultsTable
WHERE RowCnt > 50

As you can see, this can be very useful in getting to know the workings of a strange database.

Expanding on the Basic Example

But let’s take things one step further.

In addition to seeing the table name and the row count, let’s say that we also want to see a snapshot of what columns and datatypes exist in each table. This, for example, would be great for differentiating mapping tables from larger data-holding tables.

If we can get the column names and their datatypes into a single cell, then we can even write queries to further restrict the results to tables that have binary, or large text-field datatypes.

Example Code for Returning a Report of Row Count, Table Name, and Column/DataType

Below is the modified query from above, which now returns a list of each table’s columns and the column’s datatype in addition to the row count and table name.

DECLARE @TableName VARCHAR(255)
DECLARE @TmpColName VARCHAR(255)
DECLARE @ColsList VARCHAR(2000)
DECLARE @ResultsTable TABLE(
 RowCnt INT,
 TableName VARCHAR(500),
 TableCols VARCHAR(2000)
 )
DECLARE MY_CURSOR Cursor
FOR
SELECT name FROM sys.tables
ORDER BY name
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ColsList = ''
SET @TmpColName = ''
DECLARE MY_INNERCURSOR Cursor
FOR
SELECT column_name + ' (' + DATA_TYPE + ')' [MyColumns]
FROM information_schema.columns
WHERE table_name = @TableName
OPEN MY_INNERCURSOR
FETCH NEXT FROM MY_INNERCURSOR INTO @TmpColName
WHILE @@FETCH_STATUS = 0
BEGIN
 IF LEN(@ColsList)>1
 BEGIN
 SET @ColsList = @ColsList +', ' + @TmpColName
 END
 ELSE
 BEGIN
 SET @ColsList = @TmpColName
 END
FETCH NEXT FROM MY_INNERCURSOR INTO @TmpColName
END
CLOSE MY_INNERCURSOR
DEALLOCATE MY_INNERCURSOR
INSERT INTO @ResultsTable (RowCnt,TableName,TableCols)
Exec('SELECT COUNT(*),''' + @TableName + ''',''' + @ColsList + ''' FROM ' + @TableName)
FETCH NEXT FROM MY_CURSOR INTO @TableName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
SELECT RowCnt,TableName,TableCols FROM @ResultsTable
WHERE RowCnt > 50