I am trying to build a grid that will show the SQL Server table names, column name AND the extended properties that describe the column and table.
I have a table MyAppTableAndColumnInfo with columns TableName, TableDesc, ColumnName and ColumnDesc.
I am able to get the column info ok with:
but cannot get the Table extended property.
I can get the values in SSMS with:
but this does not return a resultset in AA.
Any ideas?
For interest sake, a grid with view based on:
is a great way to list all the table names and column names that I then use to quickly find where a column is used - very helpful when developing in a large app when you need to make changes.
I have a table MyAppTableAndColumnInfo with columns TableName, TableDesc, ColumnName and ColumnDesc.
I am able to get the column info ok with:
INSERT INTO MyAppTableAndColumnInfo
([TableName]
,[ColumnName]
,[ColumnDesc])
SELECT
cast(c.TABLE_NAME as char(30)),
cast(c.COLUMN_NAME as char(30)),
cast(ep.[Value] as char(100))
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
sys.columns sc
ON
OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME) = sc.[object_id]
AND c.COLUMN_NAME = sc.name
LEFT OUTER JOIN
sys.extended_properties ep
ON
sc.[object_id] = ep.major_id
AND sc.[column_id] = ep.minor_id
AND ep.class = 1
([TableName]
,[ColumnName]
,[ColumnDesc])
SELECT
cast(c.TABLE_NAME as char(30)),
cast(c.COLUMN_NAME as char(30)),
cast(ep.[Value] as char(100))
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
sys.columns sc
ON
OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME) = sc.[object_id]
AND c.COLUMN_NAME = sc.name
LEFT OUTER JOIN
sys.extended_properties ep
ON
sc.[object_id] = ep.major_id
AND sc.[column_id] = ep.minor_id
AND ep.class = 1
I can get the values in SSMS with:
SELECT
'Table' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.tables TBL
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.extended_properties SEP
ON TBL.object_id = SEP.major_id
WHERE SEP.class = 1
AND SEP.minor_id = 0
AND (SEP.value <> '1' AND SEP.value <> 1)
'Table' AS PropertyType
,SCH.name AS SchemaName
,TBL.name AS TableName
,SEP.name AS DescriptionType
,SEP.value AS DescriptionDefinition
FROM sys.tables TBL
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.extended_properties SEP
ON TBL.object_id = SEP.major_id
WHERE SEP.class = 1
AND SEP.minor_id = 0
AND (SEP.value <> '1' AND SEP.value <> 1)
Any ideas?
For interest sake, a grid with view based on:
SELECT db_name() AS DATABASE_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS
Comment