Dynamic MetaData Reader

Hi All

Hot on the heels of the dynamic info matrix, here is script to extract dynamic metadata... Same deal - run and copy and paste into excel...

if object_Id('tempdb..#Output') is not null
drop table #output
go
Set Nocount on
go
declare @DataWarehouse varchar(200) -- the name of the DW DB
,@Dimension_Convention varchar(20)
,@Fact_Convention varchar(20)
,@SurrogateKey_Convention varchar(20)

select @DataWarehouse = 'SOME_DW'
,@Dimension_Convention = '%_dim'
,@Fact_Convention = '%_fact'
,@SurrogateKey_Convention = '%_id'



Create table #Output
(
RowID int identity (1,1)
,TableType varchar(max)
,TableName varchar(max)
,ColumnName varchar(max)
,KeyColumn varchar(max)
,ExampleQuery varchar(max)
,Example varchar(max) default ''
,[Description] varchar(max)
,DataType varchar(200)
)


declare @intRow int
,@intRows int
,@Query varchar(max)

select @Query = '
Insert into #output
(
TableType
,TableName
,ColumnName
,KeyColumn
,DataType
)
select case
when SO.Name like ' +char(39) + @Dimension_Convention + char(39) + '
then ' + char(39) + 'Dimension' + char(39) + '

When SO.Name like ' +char(39) + @Dimension_Convention + char(39) + '
then ' + char(39) + 'Fact' + char(39) + '
end as TableType
,SO.Name as TableName
,SC.Name as Columnname
,SC_Key.Name as Columnname
,ST.name + case
when SC.TypeStat < 2
then ' + char(39) + char(39) + '
else ' + char(39) + ' (' + char(39) + '+ convert(varchar(20),SC.Length) + ' + char(39) + ')' + char(39) +
'end as Suffix
from ['+ @DataWarehouse + ']..SysObjects SO
join [' + @DataWarehouse + ']..SysColumns SC
on SO.ID = SC.ID
join sys.Types ST
on SC.xType = ST.system_type_ID
left outer join ['+ @DataWarehouse + ']..SysColumns SC_KEY
on SO.ID = SC_KEY.ID
and SC_Key.TypeStat = 1
Where SO.XTYPE = ' + char(39) + 'U' + char(39) + '
and (SO.Name like ' + char(39) + @Fact_Convention + char(39) + ' or SO.Name like ' + char(39) +@Dimension_Convention + char(39) + ')'

exec (@Query)

Update #Output
set ExampleQuery = 'Select top 1 ['
+ ColumnName
+ '] from ['
+ @DataWarehouse + ']..' + '['
+ TableName
+ '] where ['
+ KeyColumn
+ '] = (select max(['
+ KeyColumn
+ ']) From ['
+ @DataWarehouse + ']..' + '['
+ TableName
+ '])'


Select @intRow = min (RowID)
,@intRows = max (RowID)
from #Output

while @intRow <= @intRows
begin
Select @Query = 'Update #Output set Example=('
+ ExampleQuery
+ ') where RowID = '
+ convert(varchar(max),@intRow)
from #Output
Where RowID = @intRow

print @Query
exec (@Query)

Select @intRow = min (RowID)
,@intRows = max (RowID)
from #Output
where RowID > @intRow
end

/* Update the description logic as needed */
Update #Output
set [Description] = case
When ColumnName = KeyColumn
then 'The primary key on the ' + TableName + ' table'

When ColumnName like @SurrogateKey_Convention
Then 'A surrogate key referenced to ' + replace(ColumnName, @SurrogateKey_Convention, '')

When ColumnName like '%datasource%'
Then 'Indicates where the data came from'

When ColumnName like '%tmstamp%'
then 'The creation date and time of the transaction'

When Datatype = 'datetime'
then 'Indicates the ' + replace(replace(columnName,'date',''),'Date','') + ' date'

when right(Upper(Columnname),2) = 'NO'
Then 'The ' + Left(ColumnName,len(ColumnName)-2) + ' number'

when right(Upper(Columnname),5) = 'DESCR'
Then 'Describes the ' + left(ColumnName,len(columnName)-5)


end

select TableType
,Tablename
,ColumnName
,DataType
,isnull(Example, 'N/A') as Example
,[Description]
--,*
from #Output
Order by 1,2,3,4,5