Columns Explorer - With constraints create / drop scripts (no cursors)

,

/******* columns explorer *******/
/* Goal: returns info about each column constraint and indexes
Scope: current database

Use: execute script

---------------- result set ----------------
schemaName (nvarchar): current table schema
tableName (sysname): current table name
columnName (sysname): current column name
dataType (sysname): current column data type
maxLegth (smallint): current column data max lenght
precision (tinyint): current column data precison
scale (tinyint): current column data scale
collationName (sysname): current column collation name
isIdenty (bit): if current column is an identity column
isNullable (bit): if current column allows Null values
isFkTable (bit): if current table has foreign keys
isFkColumn (bit): if current column is a foreign key column
isPkTable (bit): if current table has a primary key
isPkColumn (bit): if current column is a primary key column
colOrderInPK (tinyint): current column ordinal in the primary key definition
isIndexedColumn (bit): if current colum is indexed
indexName (sysname): index name to which the current column belongs
indexType (varchar): index type to which the current column belongs
isUniqueIndex (bit): if [indexName] is a unique index
isUniqueConstraint (bit): if [indexName] is a unique index
idxColOrder (tinyint): current column ordinal in the [indexName] definition
hasDefault (bit): if current column has a default constraint
hasTableCheck (bit): if there's a table check constraint
hasColumnCheck (bit): if current column table check constraint
foreignKeyName (sysname): foreign key to which current column belongs
fkColumns (nvarchar): all columns of the [foreignKeyName] constraint
pkTableName (nvarchar): table name whit the primary key to which [foreignKeyName] refers to
referencedPkColumns (nvarchar): column names of the the primary key to which [foreignKeyName] refers to
fkCreateStmt (nvarchar): statement to create [foreignKeyName]
fkDropStmt (nvarchar): statement to drop [foreignKeyName]
defaultName (sysname): default constraint name for the current column
defaultValue (nvarchar): default constraint value for the current column
defaultCreateStatement (nvarchar): statement to create default constraint [defaultName]
defaultDropStatement (nvarchar): statement to drop default constraint [defaultName]
checkName (sysname): check constraint name for the current column / table
checkDefinition (nvarchar): definition of check constraint [defaultName]
checkCreateStmt (nvarchar): statement to create check constraint [defaultName]
checkDropStmt (nvarchar): statement to drop check constraint [defaultName]

lindbergh_ddv@hotmail.com (2011)
*/

with cteFk as
(select 
 a.parent_object_id as fkTableID,
        a.object_id as fkID, a.name as foreignKeyName, c.column_id as fkColumnID,
        c.name as fkColName,
        b.referenced_object_id as pkTableID,
        d.column_id as pkColumnID,
        d.name as pkColName,
        delete_referential_action,
        update_referential_action,
        is_not_for_replication
from     sys.foreign_keys a
     join sys.foreign_key_columns b
 on a.object_id=b.constraint_object_id
     join sys.columns c
 on b.parent_column_id = c.column_id
     and a.parent_object_id=c.object_id
     join sys.columns d
 on b.referenced_column_id = d.column_id
     and a.referenced_object_id = d.object_id
where object_name(b.referenced_object_id) in
    (select name from sys.tables)
)
Select distinct
        SCHEMA_NAME(tblColumns.Schema_ID) as schemaName,
     tblColumns.tableName,
        tblColumns.columnName,
        tblColumns.dataType,
        tblColumns.max_length as maxLegth,
        tblColumns.precision,
        tblColumns.scale,
        tblColumns.collation_name as [collationName],
        tblColumns.is_identity as isIdenty,
        tblColumns.is_nullable as isNullable,
        case when tblForeignKeys.fkTableID is null then 0 else 1 end as isFkTable,
        case when tblForeignKeys.fkColumnID is null then 0 else 1 end as isFkColumn,
        case when tblPrimaryKeys.pkTableID is null then 0 else 1 end as isPkTable,
        case when tblPrimaryKeys.pkColumnID is null then 0 else 1 end as isPkColumn,
        tblPrimaryKeys.ord as colOrderInPK,
        case when tblIndexes.columnID is null then 0 else 1 end as isIndexedColumn,
        tblIndexes.IndexName,
        tblindexes.indexType,
        tblindexes.is_unique as isUniqueIndex,
        tblindexes.is_unique_constraint as isUniqueConstraint,
        tblindexes.idxColOrder,
        case when tblDefaults.tableId is null then 0 else 1 end as hasDefault,
        case when tblCheckConstraints.columnId = 0 then 1 else 0 end as hasTableCheck,
        case when tblCheckConstraints.columnId is null then 0 else 
                case when tblCheckConstraints.columnId != 0 then 1 else 0 end end as hasColumnCheck         ,
        tblForeignKeys.foreignKeyName,
        Left(tblForeignKeys.fkCols,Len(tblForeignKeys.fkCols)-1) as fkColumns,
        object_name(tblForeignKeys.pkTableID) as pkTableName,
        Left(tblForeignKeys.pkCols,Len(tblForeignKeys.pkCols)-1) as referencedPkColumns,
        'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = ' +
        cast(tblForeignKeys.fkID as varchar(16)) + ' AND parent_object_id = '+ 
        cast(tblForeignKeys.fkTableID as varchar(16)) + ') ' + CHAR(13) +
        ' ALTER TABLE '+ object_name(tblForeignKeys.fkTableID)+
        ' ADD CONSTRAINT '+ tblForeignKeys.foreignKeyName +
        ' FOREIGN KEY (' + Left(tblForeignKeys.fkCols,Len(tblForeignKeys.fkCols)-1) + ') REFERENCES ' +
        object_name(tblForeignKeys.pkTableID) +
        ' (' + Left(tblForeignKeys.pkCols,Len(tblForeignKeys.pkCols)-1) + ') ' +
        ' ON DELETE' + 
        CASE delete_referential_action 
            WHEN 1 THEN N' CASCADE'
            WHEN 2 THEN N' SET NULL'
            WHEN 3 THEN N' SET DEFAULT'
            ELSE N' NO ACTION'
        END +
        ' ON UPDATE' +
        CASE UPDATE_REFERENTIAL_ACTION
            WHEN 1 THEN N' CASCADE'
            WHEN 2 THEN N' SET NULL'
            WHEN 3 THEN N' SET DEFAULT'
            ELSE N' NO ACTION'
            END +
         CASE 
            WHEN IS_NOT_FOR_REPLICATION = 1 THEN N' NOT FOR REPLICATION'
         ELSE N''
         END as fkCreateStmt,
         'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = ' +
         cast(tblForeignKeys.fkID as varchar(16)) + ' AND parent_object_id = '+ 
         cast(tblForeignKeys.fkTableID as varchar(16)) + ') ' + CHAR(13) +
         'ALTER TABLE '+object_name(tblForeignKeys.fkTableID)+
        ' DROP CONSTRAINT '+ tblForeignKeys.foreignKeyName as fkDropStmt,
        tblDefaults.defaultName,
        tblDefaults.definition as defaultValue,
        tblDefaults.createStatement as defaultCreateStatement,
        tblDefaults.dropStatement as defaultDropStatement,
        tblCheckConstraints.checkName,
        tblCheckConstraints.checkDefinition,
        tblCheckConstraints.checkCreateStmt,
        tblCheckConstraints.checkDropStmt
        
        From(Select distinct innerFk2.fkID, innerFk2.foreignKeyName, innerFk2.fkTableID, 
                        innerFk2.fkColumnID, innerFk2.pkTableID, 
                        innerFk2.delete_referential_action,
                        innerFk2.UPDATE_REFERENTIAL_ACTION,
                        innerFk2.IS_NOT_FOR_REPLICATION,
                         (Select distinct innerFk1.fkColName + ',' AS [text()]
                         From cteFk innerFk1 
                         Where innerFk1.fkTableID = innerFk2.fkTableID
                         and innerFk1.pkTableID = innerFk2.pkTableID 
                         For XML PATH ('')) [fkCols],
                         (Select distinct innerFk1.pkColName + ',' AS [text()]
                         From cteFk innerFk1 
                         Where innerFk1.fkTableID = innerFk2.fkTableID
                         and innerFk1.pkTableID = innerFk2.pkTableID 
                         For XML PATH ('')) [pkCols] 
                From cteFk innerFk2) 
            [tblForeignKeys]
            
            RIGHT join
            
            (    select 
                    t.schema_id,
                    t.object_id as tableId,
                    t.name as tableName, 
                    c.column_id as columnId,
                    c.name as columnName,
                    ty.user_type_id, 
                    ty.name as dataType,
                    c.max_length,
                    c.precision,
                    c.scale,
                    c.collation_name,
                    c.is_nullable,
                    c.is_identity
                from sys.columns c
                INNER JOIN sys.tables t
                    on c.object_id = t.object_id
                INNER JOIN sys.types ty
                    on c.user_type_id = ty.user_type_id
                )tblColumns
on tblColumns.tableId = tblForeignKeys.fkTableID
and tblColumns.columnId = tblForeignKeys.fkColumnID
left join
(
    SELECT 
         tblTables.object_id pkTableID
         ,tblIndexes.name 
         ,tblIdxCols.key_ordinal Ord 
         ,tblColumns.column_id pkColumnID 
         ,tblIndexes.type_desc 
         from sys.tables tblTables 
            inner join sys.indexes tblIndexes 
            on tblIndexes.object_id = tblTables.object_id 
            inner join sys.index_columns tblIdxCols 
            on tblIdxCols.object_id = tblTables.object_id 
            and tblIdxCols.index_id = tblIndexes.index_id 
            inner join sys.columns tblColumns 
            on tblColumns.object_id = tblTables.object_id 
            and tblColumns.column_id = tblIdxCols.column_id 
            where tblIndexes.is_primary_key = 1 
) tblPrimaryKeys
on tblColumns.tableId = tblPrimaryKeys.pkTableID
and tblColumns.columnId = tblPrimaryKeys.pkColumnID
left join
(
    SELECT
            d.parent_object_id AS tableId, 
            c.column_id as columnId, d.name as defaultName,
            d.definition,
            'IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id =' +
             cast(d.object_ID as varchar(16)) + ' AND parent_object_id =' +
             cast(d.parent_object_id as varchar(16)) + ') ' +
            ' ALTER TABLE [' + OBJECT_SCHEMA_NAME(d.parent_object_id) + '].[' +
            OBJECT_NAME(d.parent_object_id) + '] DROP CONSTRAINT [' + 
            d.name + '] ' as dropStatement,
            'IF NOT EXISTS (SELECT * FROM sys.default_constraints WHERE object_id =' +
             cast(d.object_ID as varchar(16)) + ' AND parent_object_id =' +
             cast(d.parent_object_id as varchar(16)) + ') ' +
            ' ALTER TABLE [' + OBJECT_SCHEMA_NAME(d.parent_object_id) + '].[' +
            OBJECT_NAME(d.parent_object_id) +'] ADD CONSTRAINT [' + d.name + '] DEFAULT ' 
            + d.definition + ' FOR ' + c.name as createStatement
    FROM sys.default_constraints d
    INNER JOIN sys.columns c
    ON d.parent_column_id = c.column_id
    and d.parent_object_id = c.object_id
) tblDefaults
on tblColumns.tableId = tblDefaults.tableId
and tblColumns.columnId = tblDefaults.columnId
left join
(
    SELECT    chks.object_id,
            chks.parent_object_id AS tableId, 
            c.column_id as columnId, 
            chks.name as checkName,
            chks.definition as checkDefinition,
            'IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = 277576027 AND parent_object_id = ' + 
            CAST(chks.parent_object_id as varchar(16)) + ') ' + char(13) +
            'ALTER TABLE [' + OBJECT_SCHEMA_NAME(chks.parent_object_id) + '].[' +
            OBJECT_NAME(chks.parent_object_id) + '] DROP CONSTRAINT [' + 
            chks.name + '] ' as checkDropStmt,
            'IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = 277576027 AND parent_object_id = ' + 
            CAST(chks.parent_object_id as varchar(16)) + ') ' + char(13) +
            'ALTER TABLE [' + OBJECT_SCHEMA_NAME(chks.parent_object_id) + '].[' +
            OBJECT_NAME(chks.parent_object_id) +'] WITH NOCHECK ADD CONSTRAINT [' + chks.name + '] CHECK ' 
            + chks.definition as checkCreateStmt
    FROM sys.check_constraints chks
    INNER JOIN sys.columns c
    ON chks.parent_column_id = c.column_id
    and chks.parent_object_id = c.object_id
)tblCheckConstraints
on tblCheckConstraints.tableId = tblColumns.tableId
and tblCheckConstraints.columnId = tblColumns.columnId
left join
(
select obj.object_id as tableID,
    cols.column_id AS columnID,
    idxCols.key_ordinal AS idxColOrder,
    idxs.name as indexName,
    idxs.type_desc as indexType,
    idxs.is_primary_key,
    idxs.is_unique,
    idxs.is_unique_constraint 
from sys.objects obj
    inner join sys.indexes idxs 
    on obj.object_id = idxs.object_id
    inner join sys.index_columns idxCols 
    on idxCols.object_id = idxs.object_id 
    and idxCols.index_id = idxs.index_id
    inner join sys.columns cols 
    on cols.object_id = idxCols.object_id 
    and cols.column_id = idxCols.column_id
where obj.type_desc = 'USER_TABLE' 
) tblIndexes
on tblIndexes.tableID = tblColumns.tableId
and tblIndexes.columnID = tblColumns.columnId

Rate

4.2 (5)

Share

Share

Rate

4.2 (5)