Finding all db, tables, columns on a server instance

DECLARE @sql as varchar(max)

SET @sql = ‘Select  @@servername DB_SERVER, NULL AS DB_NAME, NULL AS Table_name, NULL AS column_name, NULL AS data_type, NULL AS max_length, NULL AS is_nullable ‘

SELECT @sql=@sql+’ UNION ALL select @@servername DB_SERVER, ”’ + name + ”’ AS DB_Name
, tab.name COLLATE DATABASE_DEFAULT AS table_name
, col.name COLLATE DATABASE_DEFAULT AS column_name
, typ.name COLLATE DATABASE_DEFAULT AS data_type
, col.max_length
, col.is_nullable
FROM ‘ + name + ‘.sys.tables tab
INNER JOIN ‘ + name + ‘.sys.columns col on col.object_id = tab.object_id
INNER JOIN ‘ + name + ‘.sys.types typ on col.user_type_id = typ.user_type_id ‘ +char(10)
FROM sys.databases
WHERE name NOT IN (‘ReportServer’,’ReportServerTempDB’,’tempdb’,’master’,’model’,’msdb’)
and has_dbaccess(name)=1
EXEC(@sql)

Leave a Reply

Your email address will not be published. Required fields are marked *