Getting Field Names And Data Types from SQL Servers Catalog

0
639

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

SHARE
Previous articleIBI11 – The Mapping Process
Next articleGenerating Test Data Using Random Number Generation
Peter Nolan is one of the worlds leading thought leaders in Business Intelligence. Across his 25+ years in BI Peter has consistently invented new and innovative ways of designing and building data warehouses. SeETL now stands alone as the worlds most cost effective data warehouse development tool.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Sign up to our newsletter!