sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | List table schema


--2015.06 VERSION
--multiple keys bug FIXED!



IF exists (select * from sys.objects o where o.name = 'GetKeys')
    DROP FUNCTION GetKeys;
GO
CREATE FUNCTION GetKeys (@table_id int, @column_id_of_table int)
RETURNS @rtn TABLE (
KEYS VARCHAR(MAX)
, IsUnique VARCHAR(MAX)
, KEY_TYP VARCHAR(MAX)
, IsKeyDisabled VARCHAR(MAX)
, Extended_Property NVARCHAR(MAX)
)

AS
BEGIN 
declare @k varchar(max)  = '', @u varchar(max) = '', @t varchar(max) = '', @d varchar(max) = '', @ep nvarchar(max) = ''
    
select @k = @k + I.name + ' | '
, @u = @u + cast(I.is_unique as varchar(max)) + ' | '
, @t = @t + I.type_desc + ' | '
, @d = @d + cast(I.is_disabled as varchar(max)) + ' | '
from SYS.INDEX_COLUMNS C
INNER JOIN SYS.INDEXES I
ON I.index_id = C.index_id
AND I.object_id = C.object_id
where I.object_id = @table_id AND C.column_id = @column_id_of_table


SELECT @ep = @ep + name + ' : ' + cast(value as nvarchar(max)) + ' | '
FROM sys.extended_properties ep
WHERE class = 1 
and ep.major_id = @table_id
and ep.minor_id = @column_id_of_table;

INSERT INTO @rtn values (
 substring(@k, 1, IIF(len(@k) > 3, len(@k) - 2, 1))
, substring(@u, 1, IIF(len(@u) > 3, len(@u) - 2, 1))
, substring(@t, 1, IIF(len(@t) > 3, len(@t) - 2, 1))
, substring(@d, 1, IIF(len(@d) > 3, len(@d) - 2, 1))
, substring(@ep, 1, IIF(len(@ep) > 3, len(@ep) - 2, 1)))
RETURN
END
GO

SELECT 
--INDCOL3.index_column_id,
--KC2.object_id,
--C.object_id,
--INDCOL3.is_unique_constraint,
 UPPER(T.OBJECT_ID) OBJECT_ID
 ,UPPER(T.NAME) [TABLE NAME]
 ,UPPER(SCH.NAME) [SCHEMA NAME]
 ,UPPER(C.COLUMN_ID) COLUMN_ID
 ,UPPER(C.NAME) [COLUMN NAME]
 ,UPPER(TYP.NAME + ISNULL(CASE 
 WHEN TYP.NAME IN ('NVARCHAR', 'NTEXT', 'NCHAR') 
 THEN '(' + CASE C.MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CAST(C.MAX_LENGTH / 2 AS NVARCHAR) END + ')'
 WHEN TYP.NAME IN ('VARCHAR', 'TEXT', 'CHAR', 'BINARY', 'VARBINARY')
 THEN '(' + CASE C.MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CAST(C.MAX_LENGTH AS NVARCHAR) END + ')'
 WHEN TYP.NAME IN ('DATETIME2', 'DATETIMEOFFSET', 'TIME')
 THEN '(' + CAST(C.SCALE AS NVARCHAR) + ')'
 WHEN TYP.NAME IN ('DECIMAL', 'NUMERIC')
 THEN '(' + CAST(C.PRECISION AS NVARCHAR) + ', ' + CAST(C.SCALE AS NVARCHAR) + ')'
 END, '')) TYPE
 ,UPPER(CASE C.IS_NULLABLE WHEN 1 THEN 'NULLABLE' ELSE 'NOT NULL' END) NULLABLE
 
 ,UPPER(ISNULL(KC.TYPE, '')) PK
 , GK.*
-- ,UPPER(ISNULL(KC2.TYPE, '')) UK
 --,UPPER(ISNULL(INDCOL3.index_id, '')) IDX_ID
 ,UPPER(CASE 
 WHEN INDCOL.IS_DESCENDING_KEY IS NULL THEN '' 
 WHEN INDCOL.IS_DESCENDING_KEY = 1 THEN '1' 
 ELSE '0' END)  [DESC]
 ,UPPER(ISNULL(FK.name, '')) FK_NAME
 ,UPPER(CASE ISNULL(FKC.CONSTRAINT_COLUMN_ID, '') WHEN 0 THEN '' ELSE 'FK' END) FK
 ,UPPER(ISNULL(SCHEMA_NAME(REF.SCHEMA_ID), '')) FK_REF_TBLSCH
 ,UPPER(ISNULL(REF.NAME, '')) FK_REF_TBL
 ,UPPER(ISNULL(REFCOL.NAME, '')) FK_REF_TBLCOL
 ,UPPER(CASE C.IS_IDENTITY 
 WHEN 1 THEN 'IDENTITY(' + 
 CAST(IC.SEED_VALUE AS NVARCHAR) + ', ' + 
 CAST(IC.INCREMENT_VALUE AS NVARCHAR) + ')'  
 ELSE '' END) [IDENTITY]
 ,UPPER(C.IS_NULLABLE) IS_NULLABLE
 ,UPPER(C.MAX_LENGTH) MAX_LENGTH
 ,UPPER(C.PRECISION) [PRECISION]
 ,UPPER(C.SCALE) SCALE
 ,UPPER(C.USER_TYPE_ID) USER_TYPE_ID
 ,UPPER(C.SYSTEM_TYPE_ID) SYSTEM_TYPE_ID
 ,UPPER(C.IS_COMPUTED) IS_COMPUTED
 ,UPPER(C.default_object_id) default_object_id
 ,UPPER(ISNULL(object_definition(C.default_object_id), '')) [DEFAULT]
 --, IDX_UQ.* --, KC2.*
 --, INDCOL2.*, KC.*
 , UPPER(ISNULL(CHK.NAME, '')) CHECK_NAME
 , UPPER(ISNULL(CHK.DEFINITION, '')) CHECK_DEF
FROM 
 SYS.ALL_COLUMNS C 
 INNER JOIN SYS.TABLES T
 ON C.OBJECT_ID = T.OBJECT_ID
 INNER JOIN SYS.SCHEMAS SCH
 ON T.SCHEMA_ID = SCH.SCHEMA_ID
 INNER JOIN SYS.OBJECTS O
 ON T.OBJECT_ID = O.OBJECT_ID
 AND O.IS_MS_SHIPPED = 0
 INNER JOIN SYS.TYPES TYP
 ON C.USER_TYPE_ID = TYP.USER_TYPE_ID
 LEFT JOIN SYS.IDENTITY_COLUMNS IC
 ON IC.OBJECT_ID = C.OBJECT_ID 
 AND IC.COLUMN_ID = C.COLUMN_ID
 
 LEFT JOIN SYS.INDEX_COLUMNS INDCOL
 ON INDCOL.OBJECT_ID = T.OBJECT_ID
  AND INDCOL.COLUMN_ID = C.COLUMN_ID
  AND INDCOL.INDEX_ID = 1
  
 LEFT JOIN SYS.FOREIGN_KEY_COLUMNS FKC
 ON FKC.PARENT_COLUMN_ID = C.COLUMN_ID
 AND FKC.PARENT_OBJECT_ID = T.OBJECT_ID
 
 LEFT JOIN SYS.foreign_keys FK
 ON FK.referenced_object_id = FKC.referenced_object_id
 AND FK.parent_object_id = T.OBJECT_ID
 AND FKC.constraint_object_id = FK.object_id

 LEFT JOIN SYS.TABLES REF 
 ON REF.OBJECT_ID = FKC.REFERENCED_OBJECT_ID
 
 LEFT JOIN SYS.COLUMNS REFCOL 
 ON REFCOL.COLUMN_ID = FKC.REFERENCED_COLUMN_ID
 AND REFCOL.OBJECT_ID = REF.OBJECT_ID
 LEFT JOIN SYS.INDEXES IDX_UQ
 ON IDX_UQ.object_id = T.object_id
 AND IDX_UQ.is_primary_key = 1
 LEFT JOIN SYS.INDEX_COLUMNS INDCOL2
 ON INDCOL2.OBJECT_ID = T.OBJECT_ID
 AND C.COLUMN_ID = INDCOL2.COLUMN_ID
 AND IDX_UQ.index_id = INDCOL2.index_id
 
 LEFT JOIN SYS.KEY_CONSTRAINTS KC 
 ON T.OBJECT_ID = KC.PARENT_OBJECT_ID
  AND INDCOL2.INDEX_ID = KC.UNIQUE_INDEX_ID
  AND KC.TYPE = 'PK'
  AND KC.schema_id = SCH.schema_id

 cross apply dbo.GetKeys(T.object_id, C.column_id) GK  
 LEFT JOIN SYS.check_constraints CHK 
ON CHK.schema_id = SCH.schema_id
AND CHK.parent_object_id = C.object_id
AND CHK.parent_column_id = C.column_id
 --LEFT JOIN SYS.INDEX_COLUMNS INDCOL3
 -- ON INDCOL3.OBJECT_ID = T.OBJECT_ID
 -- AND C.COLUMN_ID = INDCOL3.COLUMN_ID
 
 --LEFT JOIN SYS.KEY_CONSTRAINTS KC2
 -- ON T.OBJECT_ID = KC2.PARENT_OBJECT_ID
 -- AND INDCOL3.INDEX_ID = KC2.UNIQUE_INDEX_ID
 -- AND KC2.schema_id = SCH.schema_id 
 -- AND KC2.TYPE = 'UQ'
 
 --LEFT JOIN SYS.OBJECTS ODF 
 --ON ODF.OBJECT_ID = C.default_object_id
 --WHERE  Extended_Property like '%xxx%'
 --CASE ISNULL(KC.TYPE, '') WHEN 'PK' THEN IDX_UQ.is_primary_key ELSE 1 END = 1
 --KC.type IS NOT NULL
 --T.NAME ='SYSTEMCOREACCOUNT'
ORDER BY [TABLE NAME], SCH.name, CAST(C.COLUMN_ID AS INT)









======for numbers of databases=====

declare @tblinfo  table (
db nvarchar(50)
,OBJECT_ID varchar(24)
,[TABLE NAME] nvarchar(128)
,[SCHEMA NAME] nvarchar(128)
,COLUMN_ID varchar(24)
,[COLUMN NAME] nvarchar(128)
,TYPE nvarchar(192)
,NULLABLE varchar(16)
,PK varchar(2)
,KEYS varchar(max)
,IsUnique varchar(max)
,KEY_TYP varchar(max)
,IsKeyDisabled varchar(max)
,Extended_Property nvarchar(max)
,[DESC] varchar(2)
,FK_NAME nvarchar(128)
,FK varchar(4)
,FK_REF_TBLSCH nvarchar(128)
,FK_REF_TBL nvarchar(128)
,FK_REF_TBLCOL nvarchar(128)
,[IDENTITY] nvarchar(72)
,IS_NULLABLE varchar(2)
,MAX_LENGTH varchar(12)
,PRECISION varchar(8)
,SCALE varchar(8)
,USER_TYPE_ID varchar(24)
,SYSTEM_TYPE_ID varchar(8)
,IS_COMPUTED varchar(2)
,default_object_id varchar(24)
,[DEFAULT] nvarchar(max)
,CHECK_NAME nvarchar(128)
,CHECK_DEF nvarchar(max))

declare @dbs table (id int identity(1,1) primary key, db nvarchar(50), enabled bit, done bit)
insert into @dbs (db, enabled, done)
select name, 1, 0 from sys.databases
where name like 'STG%'

declare @db nvarchar(50) = ''
declare @done bit = 0
declare @id int = 0
declare @cmd nvarchar(max)
while exists (select 1 from @dbs where done = 0 and enabled = 1) begin

select top (1) @db = db, @done = done, @id = id from @dbs
where done = 0 and enabled = 1




select @cmd = '
use [' + @db + ']
IF exists (select * from sys.objects o where o.name = ''GetKeys'') begin
    DROP FUNCTION GetKeys
end

declare @cmd nvarchar(max) = ''
CREATE FUNCTION GetKeys (@table_id int, @column_id_of_table int)
RETURNS @rtn TABLE (
KEYS VARCHAR(MAX)
, IsUnique VARCHAR(MAX)
, KEY_TYP VARCHAR(MAX)
, IsKeyDisabled VARCHAR(MAX)
, Extended_Property NVARCHAR(MAX)
)

AS
BEGIN 
 declare @k varchar(max)  = '''''''', @u varchar(max) = '''''''', @t varchar(max) = '''''''', @d varchar(max) = '''''''', @ep nvarchar(max) = ''''''''
 
    
 select @k = @k + I.name + '''' | ''''
 , @u = @u + cast(I.is_unique as varchar(max)) + '''' | ''''
 , @t = @t + I.type_desc + '''' | ''''
 , @d = @d + cast(I.is_disabled as varchar(max)) + '''' | ''''
 from SYS.INDEX_COLUMNS C
 INNER JOIN SYS.INDEXES I
 ON I.index_id = C.index_id
 AND I.object_id = C.object_id 
 where I.object_id = @table_id AND C.column_id = @column_id_of_table


 SELECT @ep = @ep + name + '''' : '''' + cast(value as nvarchar(max)) + '''' | ''''
 FROM sys.extended_properties ep
 WHERE class = 1 
 and ep.major_id = @table_id
 and ep.minor_id = @column_id_of_table;

 INSERT INTO @rtn values (
   substring(@k, 1, IIF(len(@k) > 3, len(@k) - 2, 1))
 , substring(@u, 1, IIF(len(@u) > 3, len(@u) - 2, 1))
 , substring(@t, 1, IIF(len(@t) > 3, len(@t) - 2, 1))
 , substring(@d, 1, IIF(len(@d) > 3, len(@d) - 2, 1))
 , substring(@ep, 1, IIF(len(@ep) > 3, len(@ep) - 2, 1)))
 RETURN
END''

exec (@cmd)
'
--select @cmd
exec (@cmd)

select @cmd = '
use [' + @db + ']
SELECT 
UPPER(''' + @db + ''') DB
 ,UPPER(T.OBJECT_ID) OBJECT_ID
 ,UPPER(T.NAME) [TABLE NAME]
 ,UPPER(SCH.NAME) [SCHEMA NAME]
 ,UPPER(C.COLUMN_ID) COLUMN_ID
 ,UPPER(C.NAME) [COLUMN NAME]
 ,UPPER(TYP.NAME + ISNULL(CASE 
 WHEN TYP.NAME IN (''NVARCHAR'', ''NTEXT'', ''NCHAR'') 
 THEN ''('' + CASE C.MAX_LENGTH WHEN -1 THEN ''MAX'' ELSE CAST(C.MAX_LENGTH / 2 AS NVARCHAR) END + '')''
 WHEN TYP.NAME IN (''VARCHAR'', ''TEXT'', ''CHAR'', ''BINARY'', ''VARBINARY'')
 THEN ''('' + CASE C.MAX_LENGTH WHEN -1 THEN ''MAX'' ELSE CAST(C.MAX_LENGTH AS NVARCHAR) END + '')''
 WHEN TYP.NAME IN (''DATETIME2'', ''DATETIMEOFFSET'', ''TIME'')
 THEN ''('' + CAST(C.SCALE AS NVARCHAR) + '')''
 WHEN TYP.NAME IN (''DECIMAL'', ''NUMERIC'')
 THEN ''('' + CAST(C.PRECISION AS NVARCHAR) + '', '' + CAST(C.SCALE AS NVARCHAR) + '')''
 END, '''')) TYPE
 ,UPPER(CASE C.IS_NULLABLE WHEN 1 THEN ''NULLABLE'' ELSE ''NOT NULL'' END) NULLABLE
 
 ,UPPER(ISNULL(KC.TYPE, '''')) PK
 , GK.*
 ,UPPER(CASE 
 WHEN INDCOL.IS_DESCENDING_KEY IS NULL THEN '''' 
 WHEN INDCOL.IS_DESCENDING_KEY = 1 THEN ''1'' 
 ELSE ''0'' END)  [DESC]
 ,UPPER(ISNULL(FK.name, '''')) FK_NAME
 ,UPPER(CASE ISNULL(FKC.CONSTRAINT_COLUMN_ID, '''') WHEN 0 THEN '''' ELSE ''FK'' END) FK
 ,UPPER(ISNULL(SCHEMA_NAME(REF.SCHEMA_ID), '''')) FK_REF_TBLSCH
 ,UPPER(ISNULL(REF.NAME, '''')) FK_REF_TBL
 ,UPPER(ISNULL(REFCOL.NAME, '''')) FK_REF_TBLCOL
 ,UPPER(CASE C.IS_IDENTITY 
 WHEN 1 THEN ''IDENTITY('' + 
 CAST(IC.SEED_VALUE AS NVARCHAR) + '', '' + 
 CAST(IC.INCREMENT_VALUE AS NVARCHAR) + '')''  
 ELSE '''' END) [IDENTITY]
 ,UPPER(C.IS_NULLABLE) IS_NULLABLE
 ,UPPER(C.MAX_LENGTH) MAX_LENGTH
 ,UPPER(C.PRECISION) [PRECISION]
 ,UPPER(C.SCALE) SCALE
 ,UPPER(C.USER_TYPE_ID) USER_TYPE_ID
 ,UPPER(C.SYSTEM_TYPE_ID) SYSTEM_TYPE_ID
 ,UPPER(C.IS_COMPUTED) IS_COMPUTED
 ,UPPER(C.default_object_id) default_object_id
 ,UPPER(ISNULL(object_definition(C.default_object_id), '''')) [DEFAULT]

 , UPPER(ISNULL(CHK.NAME, '''')) CHECK_NAME
 , UPPER(ISNULL(CHK.DEFINITION, '''')) CHECK_DEF
FROM 
 SYS.ALL_COLUMNS C 
 INNER JOIN SYS.TABLES T
 ON C.OBJECT_ID = T.OBJECT_ID
 INNER JOIN SYS.SCHEMAS SCH
 ON T.SCHEMA_ID = SCH.SCHEMA_ID
 INNER JOIN SYS.OBJECTS O
 ON T.OBJECT_ID = O.OBJECT_ID
 AND O.IS_MS_SHIPPED = 0
 INNER JOIN SYS.TYPES TYP
 ON C.USER_TYPE_ID = TYP.USER_TYPE_ID
 LEFT JOIN SYS.IDENTITY_COLUMNS IC
 ON IC.OBJECT_ID = C.OBJECT_ID 
 AND IC.COLUMN_ID = C.COLUMN_ID
 
 LEFT JOIN SYS.INDEX_COLUMNS INDCOL
 ON INDCOL.OBJECT_ID = T.OBJECT_ID
  AND INDCOL.COLUMN_ID = C.COLUMN_ID
  AND INDCOL.INDEX_ID = 1
  
 LEFT JOIN SYS.FOREIGN_KEY_COLUMNS FKC
 ON FKC.PARENT_COLUMN_ID = C.COLUMN_ID
 AND FKC.PARENT_OBJECT_ID = T.OBJECT_ID
 
 LEFT JOIN SYS.foreign_keys FK
 ON FK.referenced_object_id = FKC.referenced_object_id
 AND FK.parent_object_id = T.OBJECT_ID
 AND FKC.constraint_object_id = FK.object_id

 LEFT JOIN SYS.TABLES REF 
 ON REF.OBJECT_ID = FKC.REFERENCED_OBJECT_ID
 
 LEFT JOIN SYS.COLUMNS REFCOL 
 ON REFCOL.COLUMN_ID = FKC.REFERENCED_COLUMN_ID
 AND REFCOL.OBJECT_ID = REF.OBJECT_ID
 LEFT JOIN SYS.INDEXES IDX_UQ
 ON IDX_UQ.object_id = T.object_id
 AND IDX_UQ.is_primary_key = 1
 LEFT JOIN SYS.INDEX_COLUMNS INDCOL2
 ON INDCOL2.OBJECT_ID = T.OBJECT_ID
 AND C.COLUMN_ID = INDCOL2.COLUMN_ID
 AND IDX_UQ.index_id = INDCOL2.index_id
 
 LEFT JOIN SYS.KEY_CONSTRAINTS KC 
 ON T.OBJECT_ID = KC.PARENT_OBJECT_ID
  AND INDCOL2.INDEX_ID = KC.UNIQUE_INDEX_ID
  AND KC.TYPE = ''PK''
  AND KC.schema_id = SCH.schema_id

 cross apply dbo.GetKeys(T.object_id, C.column_id) GK  
 LEFT JOIN SYS.check_constraints CHK 
ON CHK.schema_id = SCH.schema_id
AND CHK.parent_object_id = C.object_id
AND CHK.parent_column_id = C.column_id
 
 where c.name = ''ID_CHKSUM_MOD''
ORDER BY [TABLE NAME], SCH.name, CAST(C.COLUMN_ID AS INT)
'
--select @cmd
insert into @tblinfo
exec (@cmd)

update @dbs set done = 1 where id = @id

end
BEGIN TRY
DROP TABLE tempdb..ttc
END TRY
BEGIN CATCH
O:
END CATCH
select * into tempdb..ttc from @tblinfo

select t.DB, t.[TABLE NAME], t.[SCHEMA NAME], t.CHECK_DEF from tempdb..ttc t







Comments