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

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

SELECT *
FROM FIBS.information_schema.columns

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

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

More info: SQL Server 2005 System Tables and Views
This page includes a good list of all the How-To that you might want to check out: Querying the SQL Server System Catalog FAQ

No related posts.