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.
