In my previous post, I showed some ways to query system objects in SQL Server.  If you switch between MS SQL and other databases, that's the way to go, as the information schema views comply with ANSI SQL-92 standards.  But there is another way to query information from MS SQL Server system catalogs it's by using objects called catalog views.  There's a boat load of these views available, mostly starting with 'sys' and they are queried as SELECT cols from sys.<viewname>.   If you have SQL Management Studio installed, you can find them under the Views->System Views node.

Here's a great starting point for writing your own TSQL catalog views.  But I will use the same samples from my previous post here, but rewritten to use the catalog views instead.

Retrieve a listing of tables in a database:

SELECT * FROM sys.tables

SELECT * FROM sys.views

Retrieve a listing of all the columns in a table (and their basic data types):

SELECT c.name AS column_name,c.column_id,t.name AS type_name,t.is_user_defined,t.is_assembly_type,c.max_length,c.precision,c.scale FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id WHERE c.object_id = OBJECT_ID('products')

Retrieve the primary keys of a table:

SELECT c.name AS column_name,c.column_id,t.name AS type_name,t.is_user_defined,t.is_assembly_type,c.max_length,c.precision,c.scale FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id WHERE c.object_id = OBJECT_ID('products')

Retrieve the foreign keys of a table (separate queries for PK/FK retrieval needed)

SELECT c.name AS column_name,c.column_id,t.name AS type_name,t.is_user_defined,t.is_assembly_type,c.max_length,c.precision,c.scale FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id WHERE c.object_id = OBJECT_ID('products')

Retrieve a listing of stored procedures:

SELECT * FROM sys.procedures

I keep the scripts from the previous post (and other scripts) around as I used to do much more with a variety of databases, plus I've just had those scripts hanging around for a long time as .SQL files.  However, I do use these catalog views as well, as they are quite easy to work with - and the typing's easy too!

There's a great FAQ on using these views, which I keep in my bookmarks so I can frequently access, then copy and paste from when I have to work in SQL Mgmt Studio/Enterprise Mgr.

I have several SQL utility scripts (as well as classic ASP scripts, controls, utils, etc...) that I use regularly for everything from retrieving table listings to creating select, insert, update & delete stored procedures.  Below are some of my favorite, daily use scripts. These can be used for quick documentation or for helping to create and manage objects in your database.

Retrieve a listing of all the tables and/or views in a database:

SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_type = 'BASE TABLE'

Retrieve a listing of all the columns in a table (and their basic data types):

DECLARE @tablename VARCHAR(100)

SET @tablename = 'products'

SELECT column_name, data_type, character_maximum_length FROM INFORMATION_SCHEMA.Columns WHERE table_name = @tablename

Retrieve the primary and foreign keys of a table:

DECLARE @tablename VARCHAR(100)

SET @tablename = 'products'

SELECT C.constraint_name, constraint_type FROM information_schema.table_constraints C INNER JOIN INFORMATION_SCHEMA.Key_Column_Usage K ON K.table_name=C.table_name AND K.Constraint_Name = C.Constraint_Name WHERE c.table_name=@tablename

Retrieve a listing of stored procedures:

SELECT specific_name FROM information_schema.routines WHERE routine_type = 'PROCEDURE'

All of these scripts use the INFORMATION_SCHEMA views that are built into SQL.