Table dictionary

Rédigé par Sozezzo - - Aucun commentaire

On 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


*/

Les commentaires sont fermés.