Monday, 29 December 2014

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:

Declare @SQL nvarchar(max)

Set @SQL = (select 'union all 
Select [DatabaseName] = ''''''
           ,ObjectID = O.object_id
           ,SchemaName =
           ,ObjectName =  collate latin1_general_cs_as 
           ,ColumnName =
           ,ObjectType = REPLACE(LEFT(O.type_desc,1) + RIGHT(LOWER(O.type_desc), LEN(O.type_desc)-1), ''_t'', ''_T'')
from '+QUOTENAME('.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);