List column names along with Table and Schema name in SQL Server 2008/2005

Sometime we need to find the list of columns for all table, some time column list of one table and may be one column name available in how many tables? In short finding table, column, schema of table and default value of column related information is much easier with INFORMATION_SCHEMA.Tables. Have a look at one of the short yet very handy and useful script given below.

USE AdventureWorks

GO

--list all tableName with schema, columnName and Default value of column

select TABLE_CATALOG as 'DB', TABLE_SCHEMA as 'schema', TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT

  from INFORMATION_SCHEMA.COLUMNS

 where TABLE_NAME in (select Table_name from INFORMATION_SCHEMA.tables where table_type='Base Table')

GO

--list all tableName with schema, columnName and Default value of column based on filter provided

--for columnName

select TABLE_CATALOG as 'DB', TABLE_SCHEMA as 'schema', TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT

  from INFORMATION_SCHEMA.COLUMNS

 where TABLE_NAME in (select Table_name from INFORMATION_SCHEMA.tables where table_type='Base Table')

and column_name='ProductID'

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com