Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
lindbergh_ddv
lindbergh_ddv
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 407
Comments posted to this topic are about the item Columns Explorer - With constraints create / drop scripts (no cursors)



rsorrell-839851
rsorrell-839851
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 15
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 ' '.
mark.weiers
mark.weiers
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 27
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.
lindbergh_ddv
lindbergh_ddv
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 407
Mark, you're right. Please, clean all hidden chars by an editor and script will work.

hi everybody



gsc_dba
gsc_dba
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1593 Visits: 1985
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






_____________________________________________________________________________________
[font=Courier New]gsc_dba[/font]
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8706 Visits: 885
Thanks for the script.
lindbergh_ddv
lindbergh_ddv
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 407
Thank to you. Antonio



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search