SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ColumnInfo]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[ColumnInfo]
GO

--Displays columns accessible to the current user
create  view ColumnInfo
 as
select 
	top 100 percent
	obj.name						as TABLE_NAME
	,case obj.xtype
		when 'U' then 'BASE TABLE'
		when 'V' then 'VIEW'
	end							as TABLE_TYPE
	,col.name						as COLUMN_NAME
	,col.id 						as columnid
	,col.colid						as ORDINAL_POSITION
	,com.text						as COLUMN_DEFAULT
	,case col.isnullable 
		when 1 then 'YES'
		else        'No '
	end								as IS_NULLABLE
	,spt_dtp.LOCAL_TYPE_NAME		as DATA_TYPE
	,convert(int, 
	   OdbcPrec(col.xtype, col.length, col.xprec) 
	   + spt_dtp.charbin)			as CHARACTER_MAXIMUM_LENGTH
	,convert(int, spt_dtp.charbin + 
	   case when spt_dtp.LOCAL_TYPE_NAME in ('nchar', 'nvarchar', 'ntext')
		 then  2*OdbcPrec(col.xtype, col.length, col.xprec) 
		 else  OdbcPrec(col.xtype, col.length, col.xprec) 
	   end)							as CHARACTER_OCTET_LENGTH
	,nullif(col.xprec, 0)			as NUMERIC_PRECISION
	,spt_dtp.RADIX					as NUMERIC_PRECISION_RADIX
	,col.scale						as NUMERIC_SCALE
	,spt_dtp.SQL_DATETIME_SUB		as DATETIME_PRECISION
	,convert(sysname, NULL)			as CHARACTER_SET_CATALOG
	,convert(sysname, NULL)			as CHARACTER_SET_SCHEMA
	,convert(sysname, case
		when spt_dtp.LOCAL_TYPE_NAME in 
 		('char', 'varchar', 'text')
			then a_cha.name
		when spt_dtp.LOCAL_TYPE_NAME in 
 		('nchar', 'nvarchar', 'ntext')
			then N'Unicode'
		else NULL
	end)							as CHARACTER_SET_NAME
	,convert(sysname, NULL)			as COLLATION_CATALOG
	,convert(sysname, NULL)			as COLLATION_SCHEMA
	,col.collation					as COLLATION_NAME
	,convert(sysname, case when typ.xusertype > 256  
		then DB_NAME()
	 else NULL
	end)								as DOMAIN_CATALOG
	,convert(sysname, case when typ.xusertype > 256  
			then USER_NAME(obj.uid)
		else NULL
	end)								as DOMAIN_SCHEMA
	,convert(sysname, case when typ.xusertype > 256  
			then typ.name
		else NULL
	end)								as DOMAIN_NAME
FROM
	sysobjects obj,
	master.dbo.spt_datatype_info spt_dtp,
	systypes typ,
	syscolumns col
	LEFT OUTER JOIN syscomments com on col.cdefault = com.id
		AND com.colid = 1,
	master.dbo.syscharsets		a_cha --charset/1001, not sortorder.
WHERE
	permissions(obj.id, col.name) != 0
	AND obj.id = col.id
	AND typ.xtype = spt_dtp.ss_dtype
	AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
	AND obj.xtype in ('U', 'V')
	AND col.xusertype = typ.xusertype
	AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)
	AND	a_cha.id = isnull(convert(tinyint, CollationPropertyFromID(col.collationid, 'sqlcharset')),
			convert(tinyint, ServerProperty('sqlcharset'))) -- make sure there's one and only one row selected for each column
order by
	obj.name,
	col.colid
GO

select	c.table_name as TableName,
	c.column_name as ColumnName,
	c.data_type + isnull('(' + convert(varchar, c.character_maximum_length) + ')', '') as Type,
	c.is_nullable as IsNullable,
	isnull(c.column_default, '') as DefaultValue,
	isnull(sp.value, '') as Description
from	ColumnInfo c
		left outer join dbo.sysproperties sp on sp.id = c.columnid and sp.smallid = c.ordinal_position and sp.type = 4
where	c.table_name <> 'columns'
order by
	TableName