Database Data Dictionary Utilities
This is a modification of Database Data Dictionary by rsellers at http://www.sqlservercentral.com/scripts/contributions/895.asp.
Two procedures to help maintain field definitions
vp_DatabaseDictionary lists the columns grouped by ColumnName, Type, and Length.
DatabaseSchemaByColumn is a variation of rsellers procedure in that it takes a column name as an input.
Usage: execute vp_DatabaseDictionary you may find that you have several fields of the same name with a different type. The infamous "description" field is an example.
Below Description has several types in numerous occurances of that type.
FieldName Type Length Occurances
Description ntext 16 2
Description nvarchar 80 1
Description nvarchar 100 4
Description nvarchar 300 1
Description varchar 100 1
Then executing DatabaseSchemaByColumn 'Description' gives the details of those fields allowing you to find and change them if necessary to get consistancy in your database.
CREATE PROCEDURE [DBO].[DatabaseSchemaByColumn]
@ColumnName varchar(150)
AS
SELECT Tbl.*,
ISNULL(convert(varchar, Indx.keyno),'') AS KeyNo,
ISNULL( (SELECT name FROM sysindexes WHERE Indx.indid = indid AND Tbl.id = id),'') as IndexName
FROM
sysindexkeys Indx,
(SELECT
OBJ.name AS TableName,
Cols.name AS ColumnName,
(SELECT name FROM systypes WHERE Cols.xusertype = xusertype) AS DataType,
Cols.length,
ISNULL((SELECT CONVERT(varchar(50), value) FROM sysproperties WHERE Cols.id = id AND Cols.colid = smallid),'') AS Description,
ISNULL((SELECT name FROM sysobjects WHERE id = Keys.FKeyID),'') AS FKeyTable,
ISNULL((SELECT name FROM syscolumns WHERE id = Keys.FKeyID AND colid = Keys.FKey),'') AS FKeyColumn,
ISNULL((SELECT name FROM sysobjects WHERE id = Keys.RKeyID),'') AS RKeyTable,
ISNULL((SELECT name FROM syscolumns WHERE id = Keys.RKeyID AND colid = Keys.RKey),'') AS RKeyColumn,
Cols.colorder,
Cols.ID,
Cols.colid
FROM syscolumns Cols,
sysobjects Obj,
sysforeignkeys Keys
WHERE Cols.name = @ColumnName AND Obj.xtype = 'U' AND
Cols.id = Obj.id AND
((Cols.id *= Keys.fkeyid AND Cols.colid *= Keys.fkey) OR
( Cols.id *= Keys.rkeyid AND Cols.colid *= Keys.rkey))) Tbl
WHERE
tbl.id *= Indx.id AND
tbl.colid *= Indx.colid
ORDER BY Tbl.TableName, Tbl.colorder, Indx.indid, indx.keyno
GO
Alter PROCEDURE [DBO].[vp_DatabaseDictionary] AS
SELECT Tbl.ColumnName, Tbl.DataType, Tbl.length, MIN(DISTINCT Description) as Description, COUNT(Tbl.ColumnName) as Occurances
FROM
sysindexkeys Indx,
(SELECT
Cols.name AS ColumnName,
(SELECT name FROM systypes WHERE Cols.xusertype = xusertype) AS DataType,
Cols.length,
ISNULL((SELECT CONVERT(varchar(50), value)
FROM sysproperties
WHERE Cols.id = id AND Cols.colid = smallid),'')
AS Description,
Cols.ID,
Cols.colid
FROM syscolumns Cols,
sysobjects Obj,
sysforeignkeys Keys
WHERE Obj.xtype = 'U' AND
Cols.id = Obj.id AND
((Cols.id *= Keys.fkeyid AND Cols.colid *= Keys.fkey) OR
( Cols.id *= Keys.rkeyid AND Cols.colid *= Keys.rkey))) Tbl
WHERE
tbl.id *= Indx.id AND
tbl.colid *= Indx.colid
GROUP BY Tbl.ColumnName, Tbl.DataType, Tbl.length
ORDER BY Tbl.ColumnName, Tbl.DataType, Tbl.length
GO 
