February 26, 2011 at 9:01 pm
Comments posted to this topic are about the item Columns Explorer - With constraints create / drop scripts (no cursors)
February 28, 2011 at 4:07 am
when I attempt to run the script against a SQL 2005 database, I get the following errors
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 100
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 105
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 209
Incorrect syntax near '?'.
February 28, 2011 at 8:33 am
There are probably some "hidden" characters in the text. Try copying the script into a text editor and you will see the characters that need to be removed.
February 28, 2011 at 9:28 am
Mark, you're right. Please, clean all hidden chars by an editor and script will work.
hi everybody
March 1, 2011 at 3:33 am
Very useful - thanks for sharing!
"Clean" version of code:
USE [DatabaseNameGoesHere]
;
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
gsc_dba
May 17, 2016 at 6:56 am
Thanks for the script.
May 18, 2016 at 1:47 am
Thank to you. Antonio
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply