--------------------------------------------
-- SP, FN
--------------------------------------------
SELECT *
FROM sysobjects AS aLEFT JOIN syscomments AS b ON a.id = b.id
WHERE a.type in ('P','FN')
AND b.text LIKE '%TB_YSARANGLOGIN%'
--------------------------------------------
-- TABLE
--------------------------------------------
SELECT 'DB' as [DataBase명]
, c.[TABLE_NAME] AS [테이블명(물리명)], d.[TDescription] AS [테이블상세설명]
, c.[COLUMN_NAME] AS [컬럼명(물리명)]
, d.[CDescription] AS [컬럼상세설명]
, (
CASE WHEN c.[CHARACTER_MAXIMUM_LENGTH] IS NOT NULL AND c.[DATA_TYPE] IN ('varchar', 'nvarchar', 'char') THEN c.[DATA_TYPE] + '(' + CONVERT(VARCHAR, c.[CHARACTER_MAXIMUM_LENGTH]) + ')'
ELSE c.[DATA_TYPE] END
) AS [데이터타입]
, c.[IS_NULLABLE] AS [NULL여부]
, (
CASE WHEN p.[table] IS NOT NULL AND f.[referenced_table] IS NOT NULL THEN 'PFK'
WHEN p.[table] IS NOT NULL AND f.[referenced_table] IS NULL THEN 'PK'
WHEN p.[table] IS NULL AND f.[referenced_table] IS NOT NULL THEN 'FK'
ELSE NULL END
) AS [KeyInfo]
, f.[referenced_table] AS [부모테이블]
, f.[referenced_column] AS [부모컬럼]
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON c.[TABLE_NAME] = t.[TABLE_NAME] AND t.[TABLE_TYPE] = 'BASE TABLE'
LEFT OUTER JOIN (
SELECT st.[name] AS [table],
sept.[value] AS [tdescription],
sc.[name] AS [column],
sepc.[value] AS [cdescription]
FROM sys.tables st
INNER JOIN sys.columns sc ON st.[object_id] = sc.[object_id]
LEFT OUTER JOIN sys.extended_properties sept ON st.[object_id] = sept.[major_id]
AND sept.[minor_id] = 0
AND sept.[name] = 'MS_Description'
LEFT OUTER JOIN sys.extended_properties sepc ON st.[object_id] = sepc.[major_id]
AND sc.[column_id] = sepc.[minor_id]
AND sepc.[name] = 'MS_Description'
) d ON d.[Table] = c.[TABLE_NAME] AND d.[Column] = c.[COLUMN_NAME]
LEFT OUTER JOIN (
SELECT DISTINCT tab1.[name] AS [table],
col1.[name] AS [column],
tab2.[name] AS [referenced_table],
col2.[name] AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.tables tab1 ON tab1.[object_id] = fkc.[parent_object_id]
INNER JOIN sys.columns col1 ON col1.[column_id] = parent_column_id AND col1.[object_id] = tab1.[object_id]
INNER JOIN sys.tables tab2 ON tab2.[object_id] = fkc.[referenced_object_id]
INNER JOIN sys.columns col2 ON col2.[column_id] = referenced_column_id AND col2.[object_id] = tab2.[object_id]
) f ON f.[table] = t.[TABLE_NAME] AND f.[column] = c.[COLUMN_NAME]
LEFT OUTER JOIN (
SELECT OBJECT_NAME(ic.[OBJECT_ID]) AS [table],
COL_NAME(ic.[OBJECT_ID], ic.[column_id]) AS [column]
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.[OBJECT_ID] = ic.[OBJECT_ID] AND i.[index_id] = ic.[index_id]
WHERE i.[is_primary_key] = 1
) p ON p.[table] = t.[TABLE_NAME] AND p.[column] = c.[COLUMN_NAME]
-- WHERE c.[TABLE_NAME] IN ('CARDAPP2020', 'UBE_Documents', 'MCMCANCEL', 'GROW_HEIGHTWEIGHT_2ND', 'MUKKEUMMAIN')
ORDER BY c.[TABLE_NAME]
--------------------------------------------
-- VIEW TABLE
--------------------------------------------
SELECT 'DB' as [DataBase명]
, c.[TABLE_NAME] AS [테이블명(물리명)]
, d.[TDescription] AS [테이블상세설명]
, c.[COLUMN_NAME] AS [컬럼명(물리명)]
, d.[CDescription] AS [컬럼상세설명]
, (
CASE WHEN c.[CHARACTER_MAXIMUM_LENGTH] IS NOT NULL AND c.[DATA_TYPE] IN ('varchar', 'nvarchar', 'char') THEN c.[DATA_TYPE] + '(' + CONVERT(VARCHAR, c.[CHARACTER_MAXIMUM_LENGTH]) + ')'
ELSE c.[DATA_TYPE] END
) AS [데이터타입]
, c.[IS_NULLABLE] AS [NULL여부]
, (
CASE WHEN p.[table] IS NOT NULL AND f.[referenced_table] IS NOT NULL THEN 'PFK'
WHEN p.[table] IS NOT NULL AND f.[referenced_table] IS NULL THEN 'PK'
WHEN p.[table] IS NULL AND f.[referenced_table] IS NOT NULL THEN 'FK'
ELSE NULL END
) AS [KeyInfo]
, f.[referenced_table] AS [부모테이블]
, f.[referenced_column] AS [부모컬럼]
--FROM INFORMATION_SCHEMA.TABLES t
FROM INFORMATION_SCHEMA.VIEWS t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON c.[TABLE_NAME] = t.[TABLE_NAME] --AND t.[TABLE_TYPE] = 'BASE TABLE'
LEFT OUTER JOIN (
SELECT st.[name] AS [table],
sept.[value] AS [tdescription],
sc.[name] AS [column],
sepc.[value] AS [cdescription]
FROM sys.tables st
INNER JOIN sys.columns sc ON st.[object_id] = sc.[object_id]
LEFT OUTER JOIN sys.extended_properties sept ON st.[object_id] = sept.[major_id]
AND sept.[minor_id] = 0
AND sept.[name] = 'MS_Description'
LEFT OUTER JOIN sys.extended_properties sepc ON st.[object_id] = sepc.[major_id]
AND sc.[column_id] = sepc.[minor_id]
AND sepc.[name] = 'MS_Description'
) d ON d.[Table] = c.[TABLE_NAME] AND d.[Column] = c.[COLUMN_NAME]
LEFT OUTER JOIN (
SELECT DISTINCT tab1.[name] AS [table],
col1.[name] AS [column],
tab2.[name] AS [referenced_table],
col2.[name] AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.tables tab1 ON tab1.[object_id] = fkc.[parent_object_id]
INNER JOIN sys.columns col1 ON col1.[column_id] = parent_column_id AND col1.[object_id] = tab1.[object_id]
INNER JOIN sys.tables tab2 ON tab2.[object_id] = fkc.[referenced_object_id]
INNER JOIN sys.columns col2 ON col2.[column_id] = referenced_column_id AND col2.[object_id] = tab2.[object_id]
) f ON f.[table] = t.[TABLE_NAME] AND f.[column] = c.[COLUMN_NAME]
LEFT OUTER JOIN (
SELECT OBJECT_NAME(ic.[OBJECT_ID]) AS [table],
COL_NAME(ic.[OBJECT_ID], ic.[column_id]) AS [column]
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.[OBJECT_ID] = ic.[OBJECT_ID] AND i.[index_id] = ic.[index_id]
WHERE i.[is_primary_key] = 1
) p ON p.[table] = t.[TABLE_NAME] AND p.[column] = c.[COLUMN_NAME]
--WHERE c.[TABLE_NAME] IN ('TB_')
ORDER BY c.[TABLE_NAME]
