Table dictionary
Rédigé par Sozezzo - - Aucun commentaireOn a besoin *parfois* de connaitre ou avoir la liste de toutes les tables et les colonnes
Ce script crée une table avec toutes définitions.
ex:
select * from _text_table_dictionary where Datatype = 'datetime'
PRINT @@servername; GO IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[_text_table_dictionary]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) DROP TABLE [dbo].[_text_table_dictionary] GO CREATE TABLE [dbo].[_text_table_dictionary] ([RowID] [int] IDENTITY(1, 1) NOT NULL, [TableName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TableSchema] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ColumnName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrdinalPosition] [int] NULL, [ColumnDefault] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [IsNullable] [bit] NULL, [DataType] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CharacterMaximumLength] [int] NULL, [CharacterMaximumUsed] [int] NULL, [RecordCount] [int] NULL, [IsIdentity] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [IsPrimaryKey] [bit] NULL, [IsForeignKey] [bit] NULL, [IsComputed] [bit] NULL, [ReferencedTableName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ReferencedColumnName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ObjectDescription] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [temp] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY] GO DECLARE @DataDictionary TABLE ( RowID [int] identity(1, 1) PRIMARY KEY, TableName [varchar](200), TableSchema [varchar](200), ColumnName [varchar](200), OrdinalPosition INT, ColumnDefault [varchar](200), IsNullable [bit], DataType [varchar](20), CharacterMaximumLength INT, IsIdentity [varchar](5) --[bit] , IsPrimaryKey [bit], IsForeignKey [bit], IsComputed [bit], ReferencedTableName [varchar](200), ReferencedColumnName [varchar](200), ObjectDescription [varchar](500) ) DECLARE @TableList TABLE (RowID [int] identity(1, 1) PRIMARY KEY, TableCatalog [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS, TableSchema [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS, TableName [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS, TableType [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS, ObjectDescription [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS) DECLARE @RowNumber INT, @RowCount INT, @TableName VARCHAR(255), @TableSchema VARCHAR(255), @ObjectDescription VARCHAR(500) --create a temp table to hold the primary keys --can't do INSERT EXEC with a table variable IF object_id('tempdb..#PkColumns') IS NOT NULL BEGIN DROP TABLE #PkColumns END CREATE TABLE #PkColumns (RowID [int] identity(1, 1) PRIMARY KEY, table_Qualifier VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, owner_name VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, table_name VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, column_name VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, key_seq VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, Pk_Name VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) --load the table list INSERT @TableList (TableCatalog, TableSchema, TableName, TableType, ObjectDescription) SELECT t.Table_Catalog, t.Table_Schema, t.Table_Name, t.Table_Type, CAST(IsNULL(e.value, '') AS VARCHAR(500)) FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN::FN_LISTEXTENDEDPROPERTY('MS_Description' --standard description property , 'user', 'dbo', 'table' --parent object type , @TableName -- parent object name , NULL --child object type , NULL --child object name from above ) e ON t.Table_Name = (cast(e.objname AS VARCHAR(255)) COLLATE SQL_Latin1_General_CP1_CI_AS) WHERE t.Table_Type = 'BASE TABLE' SELECT @RowCount = Count(*) FROM @TableList --loop through the tables SET @RowNumber = 1 WHILE @RowNumber <= @RowCount BEGIN SELECT @TableName = TableName, @TableSchema = TableSchema, @ObjectDescription = ObjectDescription FROM @TableList WHERE RowID = @RowNumber --clear primary keys table DELETE #PkColumns --insert the primary key records retrieved by the system stored procedure INSERT #PkColumns EXEC sp_pkeys @table_name = @TableName --insert the table name and description for header purposes INSERT @DataDictionary (TableName, ColumnName, OrdinalPosition, ColumnDefault, IsNullable, DataType, CharacterMaximumLength, IsIdentity, IsPrimaryKey, IsForeignKey, IsComputed, ReferencedTableName, ReferencedColumnName, ObjectDescription, TableSchema) VALUES ( @TableName --TableName , '' --ColumnName , 0 --OrdinalPosition , '' --ColumnDefault , 0 --IsNullable , '' --DataType , 0 --CharacterMaximumLength , 0 --IsIdentity , 0 --IsPrimaryKey , 0 --IsForeignKey , 0 --IsComputed , '' --ReferencedTableName , '' --ReferencedColumnName , @ObjectDescription --ObjectDescription , @TableSchema ) --insert the column schema information INSERT @DataDictionary (TableName, TableSchema, ColumnName, OrdinalPosition, ColumnDefault, IsNullable, DataType, CharacterMaximumLength, IsIdentity, IsPrimaryKey, IsForeignKey, IsComputed, ReferencedTableName, ReferencedColumnName, ObjectDescription) SELECT t.table_name, t.table_schema, c.column_name, c.ordinal_position, IsNULL(c.column_default, ''), CASE WHEN c.is_nullable = 'Yes' THEN 1 ELSE 0 END, c.data_type, IsNULL(c.character_maximum_length, ''), ( SELECT COLUMNPROPERTY(OBJECT_ID(t.table_name), c.Column_Name, 'IsIdentity') ) AS IsIdentity, CASE WHEN pk.column_name IS NULL THEN 0 ELSE 1 END AS IsPrimaryKey, CASE WHEN Fkey.REFERENCED_TABLE_NAME IS NULL THEN 0 ELSE 1 END AS IsForeignKey, CASE WHEN Fkey.REFERENCED_TABLE_NAME IS NULL THEN 0 ELSE 1 END AS IsComputed, IsNULL(Fkey.REFERENCED_TABLE_NAME, ''), IsNULL(Fkey.Referenced_Column_Name, ''), CAST(IsNULL(e.value, '') AS VARCHAR(500)) COLLATE SQL_Latin1_General_CP1_CI_AS AS 'ColumnDescription' FROM information_schema.tables t INNER JOIN information_schema.columns C ON t.table_name = c.table_name LEFT OUTER JOIN [#PkColumns] Pk ON PK.column_Name = c.column_Name LEFT OUTER JOIN ( SELECT CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'Enabled' ELSE 'Disabled' END AS STATUS, OBJECT_NAME(CONSTID) AS Constraint_Name, OBJECT_NAME(FKEYID) AS Table_Name, COL_NAME(FKEYID, FKEY) AS Column_Name, OBJECT_NAME(RKEYID) AS Referenced_Table_Name, COL_NAME(RKEYID, RKEY) AS Referenced_Column_Name FROM SYSFOREIGNKEYS ) AS Fkey ON c.table_name = Fkey.table_name AND c.column_Name = Fkey.Column_Name LEFT JOIN::FN_LISTEXTENDEDPROPERTY('MS_Description' --standard description property , 'user', 'dbo', 'table' --parent object type , @TableName -- parent object name from above , 'column' -- child object type , NULL -- child object name from above ) e ON c.Column_Name = e.objname COLLATE SQL_Latin1_General_CP1_CI_AS WHERE t.table_name = @TableName ORDER BY c.ordinal_position SET @RowNumber = @RowNumber + 1 END --end table loop --drop the temp table DROP TABLE #PkColumns /* SELECT * FROM @DataDictionary d ORDER BY d.TableName , d.OrdinalPosition */ DELETE FROM _text_table_dictionary INSERT INTO _text_table_dictionary (TableName, TableSchema, ColumnName, OrdinalPosition, ColumnDefault, IsNullable, DataType, CharacterMaximumLength, IsIdentity, IsPrimaryKey, IsForeignKey, ReferencedTableName, ReferencedColumnName, ObjectDescription) SELECT TableName, TableSchema, ColumnName, OrdinalPosition, ColumnDefault, IsNullable, DataType, CharacterMaximumLength, IsIdentity, IsPrimaryKey, IsForeignKey, ReferencedTableName, ReferencedColumnName, ObjectDescription FROM @DataDictionary d GO SELECT * FROM _text_table_dictionary SELECT DISTINCT TableName FROM _text_table_dictionary WHERE RecordCount > 0 --truncate table Audit --truncate table AuditConfiguration --Update _text_table_dictionary set RecordCount = ( select Count(*) as n from dbo.[User] ) Where TableName='User' GO -- Length Field BEGIN TRY DROP PROC sx_ColumnCharacterMaximumUsed END TRY BEGIN CATCH END CATCH GO CREATE PROC sx_ColumnCharacterMaximumUsed AS BEGIN DECLARE @TableName VARCHAR(200) DECLARE @TableSchema VARCHAR(200) DECLARE @ColumnName VARCHAR(200) DECLARE @Sql VARCHAR(1250) DECLARE @n INTEGER DECLARE table_cursor CURSOR FOR SELECT TableName, ColumnName, TableSchema FROM _text_table_dictionary WHERE DataType IN ('varchar', 'char', 'nvarchar') AND CharacterMaximumLength > 1000 UPDATE _text_table_dictionary SET CharacterMaximumUsed = - 1 OPEN table_cursor FETCH NEXT FROM table_cursor INTO @TableName, @ColumnName, @TableSchema WHILE @@FETCH_STATUS = 0 BEGIN PRINT '----------------------------' PRINT 'Table Name Columns: [' + @TableName + '] -> [' + @ColumnName + ']' SET @sql = '' SET @sql = @sql + ' Update _text_table_dictionary' SET @sql = @sql + ' set CharacterMaximumUsed = (' SET @sql = @sql + ' select MAX(Len(' + @ColumnName + ')) as n ' SET @sql = @sql + ' from ' + @TableSchema + '.[' + @TableName + '])' SET @sql = @sql + ' Where' SET @sql = @sql + ' ColumnName = ''' + @ColumnName + ''' ' SET @sql = @sql + ' and TableName= ''' + @TableName + ''' ' PRINT @sql EXEC (@sql) --Select @Sql = 'Select Count(*) as n from ' + @TableName FETCH NEXT FROM table_cursor INTO @TableName, @ColumnName, @TableSchema END CLOSE table_cursor DEALLOCATE table_cursor -- Record Count UPDATE _text_table_dictionary SET RecordCount = 0 DECLARE table_cursor_2 CURSOR FOR SELECT TableName, TableSchema FROM _text_table_dictionary GROUP BY TableName, TableSchema OPEN table_cursor_2 FETCH NEXT FROM table_cursor_2 INTO @TableName, @TableSchema WHILE @@FETCH_STATUS = 0 BEGIN PRINT '-----------------' SET @sql = '' SET @sql = @sql + ' Update _text_table_dictionary' SET @sql = @sql + ' set RecordCount = (' SET @sql = @sql + ' select Count(*) as n ' SET @sql = @sql + ' from ' + @TableSchema + '.[' + @TableName + '])' SET @sql = @sql + ' Where' SET @sql = @sql + ' TableName=''' + @TableName + '''' PRINT 'Table Name Count: ' + @TableName + ' ' PRINT @sql EXEC (@sql) FETCH NEXT FROM table_cursor_2 INTO @TableName, @TableSchema END CLOSE table_cursor_2 DEALLOCATE table_cursor_2 END GO /* select * from _text_table_dictionary where CharacterMaximumLength > 2048 and DataType = 'nvarchar' */ /* select * from _text_table_dictionary where ColumnName like '%ID%' */ GO /* select * from _text_table_dictionary where DataType = 'char' */ /* -- -- -- -- -- -- -- -- -- -- -- Create list of columns without timestamp -- -- -- -- -- -- -- -- -- -- drop table #temp1 go SELECT TableName, substring( stuff( ( SELECT distinct ',['+ ColumnName+']' FROM (select distinct * from _text_table_dictionary where NOT ColumnName in ('', 'Timestamp')) t1 WHERE t2.TableName = t1.TableName FOR xml path('') ) ,1,1,'') ,0,4096) as ColumnList into #temp1 FROM _text_table_dictionary t2 GROUP BY TableName ORDER BY TableName go update _text_table_dictionary set [temp] = ColumnList from #temp1, _text_table_dictionary where _text_table_dictionary.OrdinalPosition = 0 and _text_table_dictionary.TableName = #temp1.TableName go select 'print ''----------------------------------------------''; '+char(13)+char(10)+ 'print ''Copying '+TableSchema+'.['+RTRIM(TableName)+']''; '+char(13)+char(10)+ 'truncate table MesTest_DontUseIt.'+TableSchema+'.['+RTRIM(TableName)+'];'+char(13)+char(10)+ 'declare @id int;'+char(13)+char(10)+ 'set @id = 0;'+char(13)+char(10)+ 'select @id = MAX(IsIdentity) from _text_table_dictionary where TableName = ''' + RTRIM(TableName) + ''';'+char(13)+char(10)+ 'if ( @id > 0 )'+char(13)+char(10)+ 'begin'+char(13)+char(10)+ ' set IDENTITY_INSERT MesTest_DontUseIt.'+TableSchema+'.['+RTRIM(TableName)+'] on;'+char(13)+char(10)+ 'end'+char(13)+char(10)+ 'insert into MesTest_DontUseIt.'+TableSchema+'.['+RTRIM(TableName)+'] ('+RTRIM(temp)+') select '+RTRIM(temp)+' from MesProdMTL.'+TableSchema+'.['+RTRIM(TableName)+']'+char(13)+char(10)+ 'if ( @id > 0 )'+char(13)+char(10)+ 'begin'+char(13)+char(10)+ ' set IDENTITY_INSERT MesTest_DontUseIt.'+TableSchema+'.['+RTRIM(TableName)+'] off;'+char(13)+char(10)+ 'end'+char(13)+char(10)+ 'go' from _text_table_dictionary where _text_table_dictionary.OrdinalPosition = 0 and TableName <> '_text_table_dictionary' -- select * from _text_table_dictionary */ /* -- -- -- -- -- -- -- -- -- -- -- DELETE ALL CONSTRAINT -- -- -- -- -- -- -- -- -- -- SELECT 'ALTER TABLE ' + sys.schemas.name+'.['+ sys.tables.name + '] DROP CONSTRAINT [' + sys.foreign_keys.name + ']' -- SELECT sys.schemas.name+'.'+ sys.tables.name AS TableName, sys.foreign_keys.name AS Expr1 FROM sys.tables INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id INNER JOIN sys.foreign_keys ON sys.tables.object_id = sys.foreign_keys.parent_object_id */