FindTableColumnByName.sql
-- 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
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
No comments:
Post a Comment