Listing Tables and Columns in SQL 2005 using Information Schema

Just for my own note since I keep forgetting the code and also testing out the new SyntaxHighlighter plugin that I just installed. Look great isn’t it?

Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. (#)

Select a List of all Tables in a Database

1 2 3

SELECT * FROM [DB-Name].information_schema.tables WHERE table_type = 'Base Table'

Note:

• The columns returns are ‘Table_Catalog’, ‘Table_Schema’, ‘Table_Name’ and ‘Table_Type’
• The result includes dtproperties table in the list as well. To remove it add another condition in the WHERE statement.
  eg. WHERE table_type = ‘Base Table’ AND table_name != ‘dtproperties’
• Use square brackets [] if the DB name includes a dash ‘-’.

Select a list of all Tables and Columns in a Database

1 2	SELECT * FROM FIBS.information_schema.columns

23 columns resulted by the above query. To refine more, try the query below.

1 2 3 4 5 6

SELECT table_name, column_name, data_type, character_maximum_length, is_nullable FROM Information_Schema.columns WHERE table_name IN (Select name                           FROM sysobjects                           WHERE xtype = 'U') ORDER BY table_name

Note:

• xtype has two values – ‘U’ or ‘S’
• U = User generated tables
• S = System generated tables