All Columns from user tables and views

,

This script lists the columns with type and length. It eliminates system tables.

SELECT distinct
	TABLE_NAME = convert(sysname,o.name),
	COLUMN_NAME = convert(sysname,c.name),
	convert (sysname,case
		WHEN t.xusertype > 255 THEN
			t.name
		ELSE 
			d.TYPE_NAME COLLATE database_default
	END) COLUMN_TYPE,
	convert(int,case
		WHEN type_name(d.ss_dtype) IN ('numeric','decimal') THEN
			OdbcPrec(c.xtype,c.length,c.xprec)+2
		ELSE
			isnull(d.length, c.length)
	END) COLUMN_LENGTH
FROM
	sysobjects o,
	master.dbo.spt_datatype_info d,
	systypes t,
	syscolumns c
WHERE
	o.id = c.id
	AND t.xtype = d.ss_dtype
	AND c.length = isnull(d.fixlen, c.length)
	AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') AND c.number = 0))
	AND isnull(d.AUTO_INCREMENT,0) = ISNULL(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
	AND c.xusertype = t.xusertype
	AND o.name not like 'tbl_core_%'
	AND o.name not like 'sys%' 
        AND o.name not like 'db_version_1%'
        AND o.name not like 'dtproperties' 
ORDER BY TABLE_NAME, COLUMN_NAME

Rate

Share

Share

Rate