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;