ilk SQL yazımda sizin için Bir sitede rastladığım 2 SP yi size göstermek istiyorum
ilk göstereceğim Script ile Netsis DB de bulunan table'lar için create scriptleri ve constraint create scriptleri generate edebiliyoruz
bu scriptleri drop ettikten sonra kullanarak tekrar create edebiliriz.
sonraki göstereceğim script ise yine table ve bu tableların constraintlerini drop etme scriptlerini generate edebiliyorsunuz.
Sp Create Scripti
Kodumuz:
*********************************************************************************************
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
*********************************************************************************************
Kullanımına Bakılcak olursa
DB de bulunan (siparis.odemedetaylari) tablosu için create script generate etmek istersek aşağıdaki kodu kullanabiliriz.
Kodumuz:
*********************************************************************************************
EXEC usp_Create_Table_DDLs
@sTable_Name = 'siparis.odemedetaylari'
, @Create_Table_Ind = 1
, @PK_Ind = 1
, @FK_Ind = 1
, @Check_Ind = 1
, @Default_Ind = 1
*********************************************************************************
Not:
Parametrelerde bulunan 1 value değeleri scriptlerin generate edileceğini gösteriyor. Eğer değeri 0 yaparsanız o parametre ile alakalı scriptler generate edilmez.
Table ve Constraint leri Drop etmek için DDL Script Generate Etme
Kodumuz:
CREATE PROCEDURE [dbo].[usp_Drop_Table_Constraints]
(
@sConstraint_Type VARCHAR(50), -- PK, FK, CK, DF
@sDB_Name SYSNAME,
@sTable_Name SYSNAME,
@sColumn_Name SYSNAME = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sSQL VARCHAR(8000),
@sStr VARCHAR(1000)
CREATE TABLE #Temp (
Type VARCHAR(50),
DBName SYSNAME,
Schema_Name SYSNAME,
Table_Name SYSNAME,
Column_Name SYSNAME DEFAULT '',
Constraint_Name SYSNAME,
DROP_SQL VARCHAR(8000)
)
-------------------------------------------------------------
IF @sConstraint_Type = 'PK'
BEGIN
-- Drop PK
SET @sSQL = '
SELECT DISTINCT Type = ''PK'',
DBName = ''' + @sDB_Name + ''',
Schema_Name = s.name,
Table_Name = p.name,
PK_Constraint_Name = o.name,
DROP_SQL = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
FROM ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
WHERE c.TABLE_NAME = ''' + @sTable_Name + '''
AND o.type = ''PK''
'
INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Constraint_Name, DROP_SQL)
EXEC (@sSQL)
END
-------------------------------------------------------------
ELSE IF @sConstraint_Type = 'FK'
BEGIN
-- Drop FK
SET @sSQL = '
SELECT DISTINCT Type = ''FK'',
DBName = ''' + @sDB_Name + ''',
Schema_Name = s.name,
Table_Name = p.name,
FK_Constraint_Name = o.name,
DROP_SQL = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
FROM ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
WHERE c.TABLE_NAME = ''' + @sTable_Name + '''
AND o.type = ''F''
'
INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Constraint_Name, DROP_SQL)
EXEC (@sSQL)
END
-------------------------------------------------------------
ELSE IF @sConstraint_Type = 'CK'
BEGIN
-- Drop Check Constraint
SET @sSQL = '
SELECT DISTINCT Type = ''CK'',
DBName = ''' + @sDB_Name + ''',
Schema_Name = s.name,
Table_Name = p.name,
Column_Name = c.column_name,
CK_Constraint_Name = o.name,
DROP_SQL = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
FROM ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
WHERE c.TABLE_NAME = ''' + @sTable_Name + '''
AND o.type = ''C''
'
INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Column_Name, Constraint_Name, DROP_SQL)
EXEC (@sSQL)
END
-------------------------------------------------------------
ELSE IF @sConstraint_Type = 'DF'
BEGIN
-- Drop Default Constraint
SET @sSQL = '
SELECT DISTINCT Type = ''DF'',
DBName = ''' + @sDB_Name + ''',
Schema_Name = s.name,
Table_Name = p.name,
Column_Name = c.name,
DF_Constraint_Name = o.name,
DROP_SQL = ''ALTER TABLE ' + @sDB_Name + '..'' + p.name + '' DROP CONSTRAINT '' + o.name
FROM ' + @sDB_Name + '.sys.columns c
INNER JOIN ' + @sDB_Name + '.sys.objects o on c.default_object_id = o.object_id
INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
WHERE o.type = ''D''
AND c.object_id = object_id(''' + @sDB_Name + '..' + @sTable_Name + ''')
'
INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Column_Name, Constraint_Name, DROP_SQL)
EXEC (@sSQL)
END
-------------------------------------------------------------
SELECT *
FROM #Temp
-------------------------------------------------------------
END
/*
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'PK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE' -- Drop PK
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'FK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE' -- Drop FKs
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'CK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE' -- Drop Check Constraints
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'DF', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE' -- Drop Default Constraints
*/
GO
*********************************************************************************************
Örneğin db de bulunan siparisodemedetaylari tablosundaki Primary Key constraint leride drop script generate etmek istersek aşağıdaki kodu kullanabiliriz.
*********************************************************************************************
EXEC usp_Drop_Table_Constraints
@sConstraint_Type = 'PK'
, @sDB_Name = 'Netsis'
, @sTable_Name = 'siparisodemedetaylari '
*********************************************************************************************
Gene aynı şekilde Foreign Key(FK),Check(CK) ve Default(DF) Constraint leri için drop etmek istersek aşağıdaki scriptleri kullanabilirsiniz.
-- Drop FKs
EXEC usp_Drop_Table_Constraints
@sConstraint_Type = 'FK'
, @sDB_Name = 'Netsis'
, @sTable_Name = 'siparisodemedetaylari'
-- Drop Check Constraints
EXEC usp_Drop_Table_Constraints
@sConstraint_Type = 'CK'
, @sDB_Name = 'Netsis'
, @sTable_Name = 'siparisodemedetaylari'
-- Drop Default Constraints
EXEC usp_Drop_Table_Constraints
@sConstraint_Type = 'DF'
, @sDB_Name = 'Netsis'
, @sTable_Name = 'siparisodemedetaylari'
*********************************************************************************************
bu komutları kullandıktan sonra işlemlerimiz bitmiştir. hayırlı uğurlu olsun
