Getting Field Names And Data Types from SQL Servers Catalog

0
840

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')

 

Comments

comments

LEAVE A REPLY

Please enter your comment!
Please enter your name here