Skip to content

Instantly share code, notes, and snippets.

@mahmut-gundogdu

mahmut-gundogdu/gist:7810195

Created December 5, 2013 18:00
Show Gist options
  • Save mahmut-gundogdu/7810195 to your computer and use it in GitHub Desktop.
Save mahmut-gundogdu/7810195 to your computer and use it in GitHub Desktop.
Download ZIP
database in constainerlerinin create scriptini yazar. Alıntıdır. Kaynak:http://www.turgaysahtiyan.com/post/SQL-Server-Constraint-ler-icin-DROP-ve-CREATE-DDL-Scriptleri-Generate-Etme.aspx
Raw
gistfile1.txt
CREATE PROCEDURE [dbo].[usp_Create_Table_DDLs]
(
@sTable_Name SYSNAME,
@Create_Table_Ind BIT = 1,
@PK_Ind BIT = 1,
@FK_Ind BIT = 1,
@Check_Ind BIT = 1,
@Default_Ind BIT = 1
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Schema_Name SYSNAME,
@UniqueConstraints BIT = 1,
@sStr VARCHAR(MAX)
SELECT @Schema_Name = SCHEMA_NAME(schema_id)
FROM sys.objects
WHERE name = @sTable_Name
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#PKObjectID')) DROP TABLE #PKObjectID
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#Uniques')) DROP TABLE #Uniques
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#Constraints')) DROP TABLE #Constraints
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#ShowFields')) DROP TABLE #ShowFields
CREATE TABLE #Constraints (ID INT IDENTITY, Constraint_Type VARCHAR(100), SQL VARCHAR(8000), Column_Name SYSNAME DEFAULT '')
---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- Create table
IF @Create_Table_Ind = 1
BEGIN
SELECT FieldID = IDENTITY(INT,1,1),
DatabaseName = DB_NAME(),
TableOwner = TABLE_SCHEMA,
TableName = TABLE_NAME,
FieldName = COLUMN_NAME,
ColumnPosition = CAST(ORDINAL_POSITION AS INT),
ColumnDefaultValue = COLUMN_DEFAULT,
ColumnDefaultName = dobj.name,
IsNullable = CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
DataType = DATA_TYPE,
MaxLength = CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
NumericPrecision = CAST(NUMERIC_PRECISION AS INT),
NumericScale = CAST(NUMERIC_SCALE AS INT),
DomainName = DOMAIN_NAME,
FieldListingName = COLUMN_NAME + ',',
FieldDefinition = '',
IdentityColumn = CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END,
IdentitySeed = CAST(ISNULL(ic.seed_value,0) AS INT),
IdentityIncrement = CAST(ISNULL(ic.increment_value,0) AS INT),
IsCharColumn = CASE WHEN DATA_TYPE NOT IN ('TEXT') AND st.collation_name IS NOT NULL THEN 1 ELSE 0 END
INTO #ShowFields
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
WHERE c.TABLE_NAME = @sTable_Name
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
SELECT @sStr = 'CREATE TABLE ' + QUOTENAME(@Schema_Name) + '.' + QUOTENAME(@sTable_Name) + '('
SELECT @sStr = @sStr +
CHAR(10) + CHAR(9) + QUOTENAME(FieldName) + ' ' +
CASE
WHEN DomainName IS NOT NULL THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
ELSE UPPER(DataType)
+ CASE WHEN IsCharColumn = 1 OR DataType IN ('Varbinary') THEN '(' + CASE WHEN MaxLength = -1 THEN 'MAX' ELSE CAST(MaxLength AS VARCHAR(10)) END + ')' ELSE '' END
+ CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END
+ CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
--+ CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
END +
CASE WHEN FieldID = (SELECT MAX(FieldID) FROM #ShowFields) THEN '' ELSE ',' END
FROM #ShowFields
SELECT @sStr = @sStr + ')'
INSERT INTO #Constraints (Constraint_Type, SQL)
VALUES ('CREATE_TABLE', @sStr)
END
---------------------------------------------------------------------------
---------------------------------------------------------------------------
IF @PK_Ind = 1
BEGIN
PRINT 'Creating SQL for PK and Unique Constraints ...'
-- Get Object ID of the PK
SELECT DISTINCT ObjectID = cco.object_id
INTO #PKObjectID
FROM sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE OBJECT_NAME(parent_object_id) = @sTable_Name
AND i.type = 1
AND is_primary_key = 1
---------------------------------------------------------------------------
-- Get Object ID of the Uniques
SELECT DISTINCT ObjectID = cco.object_id
INTO #Uniques
FROM sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE OBJECT_NAME(parent_object_id) = @sTable_Name
AND i.type = 2
AND is_primary_key = 0
AND is_unique_constraint = 1
---------------------------------------------------------------------------
INSERT INTO #Constraints (Constraint_Type, SQL)
SELECT 'PK_UNIQUE_CONSTRAINT',
[PK_UNIQUE_CONSTRAINTS] = ISNULL('ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(@sTable_Name) + ' ADD CONSTRAINT '
+ QUOTENAME(cco.name )
+ CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
WHEN 'UQ' THEN ' UNIQUE '
END
+ CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END
+ '(' + REVERSE(SUBSTRING(REVERSE((
SELECT c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM sys.key_constraints ccok
LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE i.object_id = ccok.parent_object_id
AND ccok.object_id = cco.object_id
FOR XML PATH('')
)
), 2, 8000)) + ')', '')
FROM sys.key_constraints cco
INNER JOIN sys.schemas s ON cco.schema_id = s.schema_id
LEFT JOIN #PKObjectID pk ON cco.object_id = pk.ObjectID
LEFT JOIN #Uniques u ON cco.object_id = u.objectID
WHERE OBJECT_NAME(cco.parent_object_id) = @sTable_Name
AND (type = 'PK'
OR type = CASE WHEN @UniqueConstraints = 1 THEN 'UQ' ELSE 'PK' END
)
--AND s.name = @TableSchema
END
---------------------------------------------------------------------------
---------------------------------------------------------------------------
IF @FK_Ind = 1
BEGIN
PRINT 'Creating SQL for FK Constraints ...'
-- Create all FKs for a table
INSERT INTO #Constraints (Constraint_Type, SQL)
SELECT 'FK_CONSTRAINT',
[FK_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(a.name) + ' FOREIGN KEY (' + a.ParentColumns + ') REFERENCES ' + QUOTENAME(a.ReferencedSchema) +'.' + QUOTENAME(a.ReferencedObject) + '(' + a.ReferencedColumns + ')'
FROM
(
SELECT fk.OBJECT_ID as object_id,
ReferencedSchema = SCHEMA_NAME(o.Schema_ID),
ReferencedObject = OBJECT_NAME(fk.referenced_object_id),
ParentObject = OBJECT_NAME(fk.parent_object_id),
Name = fk.name,
ParentColumns = REVERSE(SUBSTRING(REVERSE((
SELECT cp.name + ','
FROM sys.foreign_key_columns fkc
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)
), 2, 8000)),
ReferencedColumns = REVERSE(SUBSTRING(REVERSE((
SELECT cr.name + ','
FROM sys.foreign_key_columns fkc
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)
), 2, 8000))
FROM sys.foreign_keys fk
INNER JOIN sys.objects o ON fk.referenced_object_id = o.object_id
) a
INNER JOIN sys.objects co ON a.object_id = co.object_id
INNER JOIN sys.objects o ON co.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE a.ParentObject = @sTable_Name
ORDER BY a.name
END
---------------------------------------------------------------------------
---------------------------------------------------------------------------
IF @Check_Ind = 1
BEGIN
PRINT 'Creating SQL for Check Constraints ...'
-- Create check constraints for all the columns of a table
INSERT INTO #Constraints (Constraint_Type, SQL)
SELECT 'CHECK_CONSTRAINT',
[CHECK_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' WITH CHECK ADD CHECK ' + cc.definition + ';'
FROM sys.check_constraints cc
INNER JOIN sys.objects co ON cc.object_id = co.object_id
INNER JOIN sys.objects o ON co.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE OBJECT_NAME(cc.parent_object_id) = @sTable_Name
ORDER BY o.name
END
---------------------------------------------------------------------------
---------------------------------------------------------------------------
IF @Default_Ind = 1
BEGIN
PRINT 'Creating SQL for Default Constraints ...'
-- Create defaults for all the columns of a table
INSERT INTO #Constraints (Constraint_Type, Column_Name, SQL)
SELECT 'DEFAULT_CONSTRAINT',
Column_Name = c.name,
[DEFAULT_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' ADD DEFAULT ' + sc.text + ' FOR ' + c.name
FROM syscomments sc
INNER JOIN syscolumns c ON sc.id = c.cdefault
INNER JOIN sys.objects o ON c.id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE sc.id IN (
SELECT cdefault
FROM syscolumns
WHERE id = OBJECT_ID(@Schema_Name + '.' + @sTable_Name)
AND cdefault > 0
)
ORDER BY c.name
END
---------------------------------------------------------------------------
---------------------------------------------------------------------------
SELECT *
FROM #Constraints
WHERE (1 = 1
OR Constraint_Type = CASE WHEN @Default_Ind = 1 THEN 'DEFAULT_CONSTRAINT' ELSE '' END
OR Constraint_Type = CASE WHEN @Check_Ind = 1 THEN 'CHECK_CONSTRAINT' ELSE '' END
OR Constraint_Type = CASE WHEN @PK_Ind = 1 THEN 'PK_UNIQUE_CONSTRAINT' ELSE '' END
OR Constraint_Type = CASE WHEN @FK_Ind = 1 THEN 'FK_CONSTRAINT' ELSE '' END
)
ORDER BY ID
---------------------------------------------------------------------------
PRINT 'Complete.'
END
-- EXEC usp_Create_Table_DDLs @sTable_Name = 'MY_TABLE', @Create_Table_Ind = 1, @PK_Ind = 1, @FK_Ind = 1, @Check_Ind = 1, @Default_Ind = 1
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment