Friday, November 22, 2013

Get all column name for all the table name used in database.

select TABLE_NAME, column_name, ' is used to ' as 'Description'  from information_schema.columns
 where table_name in

 (
 select TableName from (
 SELECT ta.name as TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 having SUM(pa.rows) >0
 ) v
 )
 and column_name not in('clm1','clm2','clm3','clm4','clm5','clm6','clm7')-- add column which you did not want to add in list
order by TABLE_NAME

copy with header and paste in excel.