June 5, 2007 at 2:25 pm
ISO script to render a list of all Tables, their columns, and their Datatypes within a a DB.
June 5, 2007 at 5:22 pm
I haven't had the need for this, but I found a bunch of scripts in the script section of this site. I searched for "sysobjects" since most of them use that table.
Greg
Greg
June 5, 2007 at 5:23 pm
Read about "INFORMATION_SCHEMA.COLUMNS view" in BOL.
_____________
Code for TallyGenerator
June 6, 2007 at 2:13 am
Hey,
I use the following:
SELECT o.[Name] AS [Table],c.[Name] AS [Column],t.[Name] AS [Type]
,c.[Length] as [Length]
FROM sysObjects o
INNER JOIN syscolumns c On c.ID = o.ID
INNER JOIN systypes t ON t.xUsertype = c.xtype
WHERE o.xtype = 'U'
The datalength will be the stored value i.e. for nvarchar 2 bytes per character
Any good?
Graeme
June 7, 2007 at 8:26 am
If you base your query on INFORMATION_SCHEMA views it should also work for SQL 2005.
select
c.TABLE_CATALOG
,c.TABLE_SCHEMA
,c.TABLE_NAME
,c.COLUMN_NAME
,c.DATA_TYPE
+ CASE WHEN c.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary') THEN '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' ELSE '' END
+ CASE WHEN c.DATA_TYPE IN ('decimal', 'numeric') THEN '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR) + ')' ELSE '' END
+ CASE WHEN c.IS_NULLABLE = 'No' THEN ' NOT NULL ' ELSE ' NULL' END
+ ISNULL(' DEFAULT ' + c.COLUMN_DEFAULT, '')
from information_schema.columns c
inner join information_schema.tables t on t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_NAME = c.TABLE_NAME
where t.TABLE_TYPE = 'BASE TABLE'
order by c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply