I added a little based on another Groups post I found to list all the
column properties and descriptions of every table in my database using
this query:
--make a temporary table to hold list of table names
CREATE table #tablelist (table_name varchar(20))
INSERT INTO #tablelist --find all tables in the database SELECT
table_name FROM information_schema.tables WHERE table_type='BASE TABLE'
--create a cursor loop over temp table to get all meta data
DECLARE @table_name varchar(20)
DECLARE table_Cursor CURSOR FOR
SELECT table_name from #tablelist
OPEN table_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM table_Cursor into @table_name
SELECT
table_name,
column_name,
data_type,
cast(des.value AS VARCHAR(4000)) AS col_desc
FROM information_schema.Columns col
LEFT OUTER JOIN
::fn_listextendedproperty(NULL,'user','dbo','table ',@table_name,'column',default)
des
ON col.column_name=des.objname
WHERE table_name=@table_name
ORDER BY ORDINAL_POSITION
END
CLOSE table_Cursor
DEALLOCATE table_Cursor
DROP TABLE #tablelist