Systems Engineering and RDBMS

Archive for March 26th, 2007

One more meta-data script for SQL Server 2005

Posted by decipherinfosys on March 26, 2007

Here’s another script to get a list of all the tables, their columns, nullability constraint, data-type, data-type lengths and column default values:

select table_name, column_name, is_nullable, data_type,
case when data_type in (‘int’, ‘smallint’, ‘tinyint’, ‘bigint’ ) then cast(numeric_precision as varchar(10))
when data_type in (‘numeric’, ‘decimal’, ‘float’ ) then ‘Numeric(‘ + cast(numeric_precision as varchar(10)) + ‘,’ + cast(numeric_scale as varchar(10))+ ‘ )’
when data_type in (‘datetime’, ‘smalldatetime’) then cast(datetime_precision as varchar(10))
when data_type = ‘bit’ then ‘1’
when data_type in (‘char’, ‘nchar’, ‘text’, ‘ntext’, ‘varchar’, ‘nvarchar’ ) then case cast(character_maximum_length as varchar(10))
when -1 then ‘varchar(max)’
else cast(character_maximum_length as varchar(10))
end
when data_type = ‘xml’ then ‘**XML**’
when data_type = ‘sql_variant’ then ‘**Variable data-type**’
else data_type
end as data_type_length,
column_default as Default_Value
from information_schema.columns
where table_name in (select table_name from information_schema.tables where table_type = ‘Base Table’)
order by table_name, ordinal_position

Posted in SQL Server | Leave a Comment »

A very simple way of pivoting row to column data in SQL Server

Posted by decipherinfosys on March 26, 2007

We had covered pivoting and un-pivoting of data in one of our previous post related to our whitepaper on the subject.  Here is yet another very simple way of doing pivoting if you need to get the data in a variable.

declare @str varchar(8000)
set @str = ”
select @str = @str + ‘, ‘ + name
from sysobjects
where xtype = ‘u’
order by name
print substring(@str, 3, len(@str))

This will prepare a comma separated list of all the tables in the schema.  If your list is huge, you can replace varchar(8000) with varchar(max) in SQL Server 2005.  This technique can be used in different places within the application code as well and works in SQL Server 2000 as well as SQL Server 2005.

Posted in SQL Server | Leave a Comment »