Исследуем Бд mssql с помощью tsql скриптов

Tsql теория > Исследуем Бд mssql с помощью tsql скриптов
30.03.2019 12:55:03


Наиболее часто встречающиеся слова в статье:

[TableName] [objects] [Servername] [DB_NAME] [SchemaName] [object_id] [parent_object_id] [referenced_object_id] [Table_Type] [INFORMATION_SCHEMA]


Статья:

--состав таблиц
SELECT  @@Servername AS Server ,
        DB_NAME() AS DBName ,
        isc.Table_Name AS TableName ,
        isc.Table_Schema AS SchemaName ,
        Ordinal_Position AS  Ord ,
        Column_Name ,
        Data_Type ,
        Numeric_Precision AS  Prec ,
        Numeric_Scale AS  Scale ,
        Character_Maximum_Length AS LEN , -- -1 means MAX like Varchar(MAX) 
        Is_Nullable ,
        Column_Default ,
        Table_Type
FROM     INFORMATION_SCHEMA.COLUMNS isc
        INNER JOIN  information_schema.tables ist
              ON isc.table_name = ist.table_name 
--      WHERE Table_Type = ''BASE TABLE'' -- ''Base Table'' or ''View'' 
ORDER BY DBName ,
        TableName ,
        SchemaName ,
        Ordinal_position; 
 
 
--индексы
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.Name AS TableName ,
        i.Name AS IndexName,i.type_desc
FROM    sys.objects o
        INNER JOIN sys.indexes i ON o.object_id = i.object_id
WHERE   o.Type = ''U'' -- User table 
        AND LEFT(i.Name, 1) <> ''_'' -- Remove hypothetical indexes 
ORDER BY o.NAME ,
        i.name; 
 
--
--внешние ключи
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        parent.name AS ''TableName'' ,
        o.name AS ''ForeignKey'' ,
        o.[Type] ,
        o.Create_date
FROM    sys.objects o
        INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE   o.[Type] = ''F'' -- Foreign Keys 
ORDER BY parent.name ,
        o.name 
-- получение таблиц из схемы
declare @db varchar(100)
declare @tsql varchar(max)
select @db=DB_NAME() 
select @tsql =''SELECT TABLE_NAME, TABLE_SCHEMA
                        FROM [''+@db+''].INFORMATION_SCHEMA.TABLES
                        WHERE TABLE_TYPE = ''''BASE TABLE'''''';
exec (@tsql) 
 
-- получить внешние ключи и к чему относятся
SELECT  f.name AS ForeignKey ,
        SCHEMA_NAME(f.SCHEMA_ID) AS SchemaName ,
        OBJECT_NAME(f.parent_object_id) AS TableName ,
        COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName ,
        SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName ,
        OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName ,
        COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
                                              AS ReferenceColumnName
FROM    sys.foreign_keys AS f
        INNER JOIN sys.foreign_key_columns AS fc
               ON f.OBJECT_ID = fc.constraint_object_id
        INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ORDER BY TableName ,
        ReferenceTableName;