T-SQL Database Explorer Script

Dynamic SQL script to query all objects within a SQL instance for use in Qlikview.


This requires relatively high permissions on a server to run:

LOAD *;
SQL 
Declare @SQL nvarchar(max)

Set @SQL = (select 'union all 
Select [DatabaseName] = '''+D.name+'''
           ,ObjectID = O.object_id
           ,SchemaName = s.name
           ,ObjectName = O.name  collate latin1_general_cs_as 
           ,ColumnName = c.name
           ,ObjectType = REPLACE(LEFT(O.type_desc,1) + RIGHT(LOWER(O.type_desc), LEN(O.type_desc)-1), ''_t'', ''_T'')
from '+QUOTENAME(D.name)+'.sys.objects O
LEFT JOIN SYS.Schemas S ON S.schema_id = O.schema_id
LEFT JOIN SYS.Columns C ON C.object_id = O.object_id
'
from Master.SYS.Databases D
Where D.state_desc<>'OFFLINE'
For xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

exec (@SQL);