Hi! Thanks for dropping by my blog. It is much appreciated!
Todays entry is just a simple thing that I use time and time again as I work on BI projects.
It is a way of getting the mapping of the tables and columns, including data types, from the sql server catalog. Indeed, I was using it today to build indexes and so I thought I would just post it and share it.
Here is the view. I hope you can make use of it on your projects!
Best Regards
Peter
create view [dbo].[z01_select_columns_02] as
select
a.name owner
,b.name tablename
,c.name column_name
,c.colorder column_order
,case d.name
when 'tinyint' then 'tinyint'
when 'smallint' then 'smallint'
when 'int' then 'integer'
when 'bigint' then 'bigint'
when 'money' then 'money'
when 'text' then 'nvarchar' + '(' + cast(c.length as varchar(255)) + ')'
when 'varchar' then 'varchar' + '(' + cast(c.prec as varchar(255)) + ')'
when 'nvarchar' then 'nvarchar' + '(' + cast(c.prec as varchar(255)) + ')'
when 'char' then 'char' + '(' + cast(c.prec as varchar(255)) + ')'
when 'decimal' then 'decimal' + '(' + cast(c.prec as varchar(255)) + ',' + cast(c.scale as varchar(255)) + ')'
when 'uniqueidentifier' then 'nvarchar' + '(' + cast((c.length * 4) as varchar(255)) + ')'
when 'bit' then 'bit'
when 'datetime' then 'datetime'
when 'smalldatetime' then 'smalldatetime'
when 'image' then 'image'
when 'float' then 'float'
when 'ntext' then 'nvarchar (4000)'
when 'timestamp' then 'varchar(255)'
when 'varbinary' then 'varbinary' + '(' + cast(c.prec as varchar(255)) + ')'
else
'unknown'
end as datatype_deploy
,case d.name
when 'tinyint' then 'tinyint'
when 'smallint' then 'smallint'
when 'int' then 'integer'
when 'bigint' then 'bigint'
when 'money' then 'money'
when 'text' then 'nvarchar' + '(' + cast(c.length as varchar(255)) + ')'
when 'varchar' then 'varchar' + '(' + cast(c.prec as varchar(255)) + ')'
when 'nvarchar' then 'nvarchar' + '(' + cast(c.prec as varchar(255)) + ')'
when 'char' then 'char' + '(' + cast(c.prec as varchar(255)) + ')'
when 'decimal' then 'decimal' + '(' + cast(c.prec as varchar(255)) + ',' + cast(c.scale as varchar(255)) + ')'
when 'uniqueidentifier' then 'nvarchar' + '(' + cast((c.length * 4) as varchar(255)) + ')'
when 'bit' then 'bit'
when 'datetime' then 'datetime'
when 'smalldatetime' then 'smalldatetime'
when 'image' then 'image'
when 'float' then 'float'
when 'ntext' then 'nvarchar (4000)'
when 'timestamp' then 'varchar(255)'
when 'varbinary' then 'varbinary' + '(' + cast(c.prec as varchar(255)) + ')'
else
'unknown'
end as datatype_documentation
, ' ' null_clause_deploy
, ' ' null_clause_documentation
, ' ' other_clause
,d.name datatype_name
,c.length length
,c.prec precision
,c.scale scale
,c.isnullable nullable
,c.type type_col
from
sysusers a
,sysobjects b
,syscolumns c
,systypes d
where 1=1
and a.uid = b.uid
/*and a.name = 'M1'*/
and b.xtype = 'U'
--and b.name in ('stg_email_sol_cancel','stg_solicitation_txn','Transactions')
and c.id = b.id
and d.xtype = c.xtype
and not (d.name = 'sysname')
