SQL 2000
2005 2008 2012 Find any Table, Column, & Data Type by Name
|
-- Parameter
Declaration
DECLARE @TableName
VARCHAR(250)
,
@ColumnName VARCHAR(250)
, @DataType
VARCHAR(250)
-- Table to be
found, Any Column, Any DataType
SELECT
@TableName =
'AddressType'
,
@ColumnName = '%'
, @DataType
= NULL
SELECT
ISC.TABLE_SCHEMA
AS TableSchema
, ISC.TABLE_NAME AS
TableName
, ISC.COLUMN_NAME AS
ColumnName
, UPPER(ISC.DATA_TYPE) +
COALESCE('('+CAST(ISC.CHARACTER_MAXIMUM_LENGTH
AS VARCHAR)+')','')
AS DataType
, ISNULL(ISC.COLLATION_NAME,'') AS ColumnCollation
, ISNULL(CASE
WHEN SC.id IS NOT NULL THEN 'IDENTITY(' +
CAST(IDENT_SEED(ISC.TABLE_SCHEMA + '.' + ISC.TABLE_NAME) AS VARCHAR) + ',' +
CAST(IDENT_INCR(ISC.TABLE_SCHEMA + '.' + ISC.TABLE_NAME) AS VARCHAR) + ')'
END,'') AS [Identity]
, CASE
WHEN
ISC.IS_NULLABLE =
'No' THEN 'NOT '
ELSE ''
END + 'NULL' AS Nullable
, ISNULL(ISC.COLUMN_DEFAULT,'') AS ColumnDefault
, ISNULL(ISCCU.CONSTRAINT_NAME,'') AS ColumnConstraint
FROM INFORMATION_SCHEMA.COLUMNS ISC
LEFT OUTER JOIN syscolumns SC
ON OBJECT_NAME(SC.id) = ISC.TABLE_NAME
AND
SC.name = ISC.COLUMN_NAME
AND
COLUMNPROPERTY(SC.id, SC.name, 'IsIdentity') = 1
LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ISCCU
ON
ISCCU.TABLE_NAME =
ISC.TABLE_NAME
AND
ISCCU.COLUMN_NAME =
ISC.COLUMN_NAME
WHERE 1 = 1
AND ISC.TABLE_NAME LIKE ISNULL(@TableName,'%')
AND ISC.COLUMN_NAME LIKE ISNULL(@ColumnName,'%')
AND ISC.DATA_TYPE LIKE ISNULL(@DataType,'%')
ORDER BY ISC.TABLE_NAME, ISC.COLUMN_NAME
TableSchema
|
TableName
|
ColumnName
|
DataType
|
ColumnCollation
|
Person
|
AddressType
|
AddressTypeID
|
INT
|
|
Person
|
AddressType
|
ModifiedDate
|
DATETIME
|
|
Person
|
AddressType
|
Name
|
NVARCHAR(50)
|
Latin1_General_CS_AS
|
Person
|
AddressType
|
rowguid
|
UNIQUEIDENTIFIER
|
|
Continued
|
||||
Identity
|
Nullable
|
ColumnDefault
|
ColumnConstraint
|
IDENTITY(1,1)
|
NULL
|
PK_AddressType_AddressTypeID
|
|
NULL
|
(getdate())
|
||
NULL
|
|||
NULL
|
(newid())
|
