Viewing sample rows in all used tables

During database migration projects there is sometimes the need to search through databases looking for clues to assist with the migration i.e. connection strings, configuration settings etc.

This can be a daunting task, especially when a database holds hundreds or even thousands of tables.

The following query may help if you find yourself in this situation.

1. Run the following query against the database. It should identify non-empty tables and generate the relevant TSQL select queries.

SELECT 'SELECT TOP 5 ''[' + OBJECT_SCHEMA_NAME(a.object_id) + '].[' + a.name + ']'' AS Table_Name, * FROM [' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(a.object_id) + '].[' + a.name + '] WITH (nolock);'  
FROM sys.objects a, sysindexes b
WHERE 1=1
AND a.object_id = b.id -- match objects
AND b.indid IN (0,1) -- 0 is heap 1 is clustered
AND a.type_desc = 'USER_TABLE' -- only user tables
AND b.dpages <> 0 -- data in the table
ORDER BY a.name;

image1
2. Run the generated TSQL select queries and progress with your investigations.

image2

Leave a Reply