how-to create a table describing your columns in mssql 2005 2008

/****** Object:  Table [dbo].[MetaColumn]    Script Date: 03/19/2010 14:42:26 ******/

IF  EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[MetaColumn]') AND type in (N'U'))

DROP TABLE [dbo].[MetaColumn]

GO

/****** Object:  Table [dbo].[MetaColumn]    Script Date: 03/19/2010 14:42:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[MetaColumn](

[MetaColumnId] [int] IDENTITY(1,1) NOT NULL,

[ObjectId] [int] NULL,

[DbName] [varchar](256) NOT NULL,

[TableSchema] [varchar](256) NOT NULL,

[TableName] [varchar](256) NOT NULL,

[ColName] [varchar](256) NULL,

[OrdinalPosition] [int] NULL,

[DataType] [varchar](200) NULL,

[NumericPrecision] [int] NULL ,

[NumericScale] [int] NULL ,

[IsNullable] [bit] NULL,

[IsIdentity]  [bit] NULL,

[IsIndex]  [bit] NULL,

[MaxLength] [int] NULL,

CONSTRAINT [PK_MetaColumn] PRIMARY KEY CLUSTERED 

(

[MetaColumnId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

/* <doc> 

Used describiing of the column of the tables in this database instance 

</doc> */

GO

SET NOCOUNT ON;                

SET XACT_ABORT ON;                

GO                

--SET IDENTITY_INSERT [dbo].[MetaColumn] ON;                

GO                

INSERT INTO [MetaColumn]( 

[ObjectId] --type of int

, [DbName] --type of varchar

, [TableSchema] --type of varchar

, [TableName] --type of varchar

, [ColName] --type of varchar

, [OrdinalPosition] --type of int

, [DataType] --type of varchar

, [NumericPrecision] --type of int

, [NumericScale] --type of int

, [IsNullable] --type of bit

, [IsIdentity] --type of bit

, [IsIndex] --type of bit

, [MaxLength] --type of int

) 

SELECT  

ROW_NUMBER() over( ORDER BY Cols.TABLE_NAME, Cols.COLUMN_NAME , Cols.ORDINAL_POSITION    ) , 

DB_NAME() as 'DbName' ,

Cols.TABLE_SCHEMA as 'TableSchema', 

Cols.TABLE_NAME as 'TableName',

Cols.COLUMN_NAME as 'ColName',

Cols.ORDINAL_POSITION as 'OrdinalPosition',

Cols.DATA_TYPE as 'DataType',

Cols.NUMERIC_PRECISION as 'NumericPrecision',

Cols.NUMERIC_SCALE as 'NumericScale', 

Cols.IS_NULLABLE as 'IsNullable',

Cols.CHARACTER_MAXIMUM_LENGTH as 'MaxLength' , 

COLUMNPROPERTY(object_id(Cols.TABLE_NAME), 

Cols.COLUMN_NAME, 'IsIdentity') AS 'IsIdentity',

( SELECT COUNT(KCU.COLUMN_NAME)

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 

ON KCU.TABLE_NAME = TC.TABLE_NAME

AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

WHERE KCU.TABLE_NAME = Cols.TABLE_NAME

AND KCU.COLUMN_NAME = Cols.COLUMN_NAME

) AS 'IsIndex'

FROM [INFORMATION_SCHEMA].[COLUMNS] Cols

ORDER BY Cols.TABLE_NAME, Cols.COLUMN_NAME , Cols.ORDINAL_POSITION 

--SET IDENTITY_INSERT [dbo].[MetaColumn] OFF; 

Select top 5  * from [MetaColumn] order by 1 desc   

--SELECT NAME FROM SYS.PROCEDURES WHERE NAME LIKE '%PROC%'

--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%TABLE%'

--exec sp_HelpText procUtils_GenerateMetaInsert

--exec procUtils_GenerateMetaInsert 'MetaColumn'

-- DELETE FROM MetaColumn WHERE MetaColumnId = n