SQL Server 2005 includes several new veiws to help developers fetch metadata about database objects. This has been possible in previous version of SQL Server, but required messy queries against the internal system tables or the use of some of the stock system procedures that shipped with SQL Server.
The database schema we develop with at work is fairly large - ~2000 tables, ~4500 procs. I found myself using two of the most famous system procedures - sp_help and sp_helptext - to frequently lookup table and proc metadata.
These are handy stored procedures, but sp_help lacks easy to read foreign key info and I'd rather not have a two different procs to think about for looking up metadata. INFORMATION_SCHEMA views are views that ship with SQL Server 2005 that let you see the raw schema information about your tables, views, UDF's, and stored procs.
I put together a few procs of my own that use the INFORMATION_SCHEMA views to pull the meta data I want.
sp_table_info
What I really want to know about a table is it's columns, their data types, any foreign keys it possess, and the table and primary key a given foreign key maps to. sp_table_info does just that in a simple result set.
sp_proc_info
This is little more than a wrapper for sp_helptext, but add a list of parameters that are easily copied.
sp_info
This proc is a wrapper for both sp_table_info and sp_proc_info and gives me a single point of entry for both object type (one syntax).
sp_info 'Sales by Year'
sp_info Products
Scripts
Here are the scripts to create these guys if you'd like to use them
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_table_info]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_table_info]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_table_info]
@name nvarchar(500)
AS
SELECT
columns.COLUMN_NAME as ColumnName,
columns.DATA_TYPE as DataType,
columns.CHARACTER_MAXIMUM_LENGTH as CharacterMaxLen,
columns.COLUMN_DEFAULT as ColumnDefault,
columns.IS_NULLABLE as IsNullable,
column_constraints.CONSTRAINT_NAME as ConstraintName,
rel_info.table_name as ForeignKeyTable,
rel_info.column_name as ForeignKeyColumn
FROM
INFORMATION_SCHEMA.columns columns
LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE column_constraints
ON column_constraints.TABLE_NAME = @name AND column_constraints.COLUMN_NAME = columns.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table_info
ON column_constraints.CONSTRAINT_NAME = table_info.CONSTRAINT_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rel_info
ON table_info.unique_constraint_name = rel_info.constraint_name
WHERE
columns.TABLE_NAME = @name
ORDER BY columns.ordinal_position
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_proc_parameters]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_proc_parameters]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_proc_parameters]
@name nvarchar(500)
AS
SELECT
PARAMETER_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
PARAMETER_MODE
FROM
information_schema.parameters
WHERE
SPECIFIC_NAME = @name
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_proc_info]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_proc_info]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_proc_info]
@name nvarchar(500)
AS
EXEC sp_proc_parameters @name
EXEC sp_helptext @name
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_info]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_info]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_info]
@name nvarchar(100)
AS
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@name) AND type in (N'P', N'PC')) BEGIN
EXEC sp_proc_info @name
END ELSE IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@name) AND type in (N'U')) BEGIN
EXEC sp_table_info @name
END
GO