How to generate create script of tables with their constraints using SQL query in SQL Server 2008
Try this one -
DECLARE @object_id INT, @object_name SYSNAME
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT o.[object_id], '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U'
AND o.is_ms_shipped = 0
OPEN cur
FETCH NEXT FROM cur INTO @object_id, @object_name
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @SQL NVARCHAR(MAX) = ''
;WITH index_column AS
(
SELECT
ic.[object_id]
, ic.index_id
, ic.is_descending_key
, ic.is_included_column
, c.name
FROM sys.index_columns ic WITH (NOWAIT)
JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
WHERE ic.[object_id] = @object_id
),
fk_columns AS
(
SELECT
k.constraint_object_id
, cname = c.name
, rcname = rc.name
FROM sys.foreign_key_columns k WITH (NOWAIT)
JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id
JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
WHERE k.parent_object_id = @object_id
)
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(9) + ', [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + cc.[definition]
ELSE UPPER(tp.name) +
CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END +
CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END
END + CHAR(13)
FROM sys.columns c WITH (NOWAIT)
JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
+ ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +
(SELECT STUFF((
SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
FROM sys.index_columns ic WITH (NOWAIT)
JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE ic.is_included_column = 0
AND ic.[object_id] = k.parent_object_id
AND ic.index_id = k.unique_index_id
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
+ ')' + CHAR(13)
FROM sys.key_constraints k WITH (NOWAIT)
WHERE k.parent_object_id = @object_id
AND k.[type] = 'PK'), '') + ')' + CHAR(13)
+ ISNULL((SELECT (
SELECT CHAR(13) +
'ALTER TABLE ' + @object_name + ' WITH'
+ CASE WHEN fk.is_not_trusted = 1
THEN ' NOCHECK'
ELSE ' CHECK'
END +
' ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY('
+ STUFF((
SELECT ', [' + k.cname + ']'
FROM fk_columns k
WHERE k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')' +
' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
+ STUFF((
SELECT ', [' + k.rcname + ']'
FROM fk_columns k
WHERE k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')'
+ CASE
WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'
WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'
ELSE ''
END
+ CASE
WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'
ELSE ''
END
+ CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name + ']' + CHAR(13)
FROM sys.foreign_keys fk WITH (NOWAIT)
JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
WHERE fk.parent_object_id = @object_id
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
+ ISNULL(((SELECT
CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END
+ ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' +
STUFF((
SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
FROM index_column c
WHERE c.is_included_column = 0
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
+ ISNULL(CHAR(13) + 'INCLUDE (' +
STUFF((
SELECT ', [' + c.name + ']'
FROM index_column c
WHERE c.is_included_column = 1
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + CHAR(13)
FROM sys.indexes i WITH (NOWAIT)
WHERE i.[object_id] = @object_id
AND i.is_primary_key = 0
AND i.[type] = 2
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
), '') + CHAR(13) + 'GO'
PRINT @SQL
FETCH NEXT FROM cur INTO @object_id, @object_name
END
CLOSE cur
DEALLOCATE cur
Output -
CREATE TABLE [dbo].[test1]
(
[ID] INT NOT NULL
, CONSTRAINT [PK_test1_ID] PRIMARY KEY ([ID] ASC)
)
GO
CREATE TABLE [dbo].[tbl_name]
(
[ID] INT NOT NULL
, CONSTRAINT [PK_tbl_name_ID] PRIMARY KEY ([ID] ASC)
)
GO
how to generate a script for the entire database
This is indeed because you didn't name your constraints, so the second time it attempts to drop the old name then add an additional constraint.
The first time you run the script it checks for a default constraint called DF__LookupCol__IsPri__46DD686B, which of course does not exist.
Then it runs
ALTER TABLE [cfg].[LookupColumns] ADD DEFAULT (CONVERT([bit],(0))) FOR [IsPrimaryKey]
Which creates a new constraint with a new auto-generated name. Next time you run the script, it tries to drop DF__LookupCol__IsPri__46DD686B again, which still doesn't exist, and then tries to add a new constraint, which fails.
So to be able to re-run this script, you should name all your constraints and indexes before generating the scripts.
You can find the system-named default constraints like this:
select name, object_name(object_id) table_name
from sys.default_constraints
where is_system_named = 1
select name, object_name(object_id) table_name
from sys.check_constraints
where is_system_named = 1
select name, object_name(object_id) table_name
from sys.key_constraints
where is_system_named = 1
etc
Or you can look for the __ in the names, but you would want to manually review those of course.
Running database creation script with constraints
There are two issues with the foreign key constraints:
1. Adding the Constraints
When there are FKs that link subsets of tables in cycles you can create the tables first, and then add the constraints later.
For example:
CREATE TABLE store (
id INTEGER NOT NULL,
nome VARCHAR(255) NOT NULL,
document VARCHAR(80) NOT NULL,
store_product INTEGER NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE product (
id INTEGER NOT NULL,
nome VARCHAR(255) NOT NULL,
price NUMERIC(15,2) NOT NULL,
store_id INTEGER NOT NULL,
inventory_id INTEGER NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE inventory (
id INTEGER NOT NULL PRIMARY KEY,
amount INTEGER NOT NULL,
product_id INTEGER NOT NULL
);
And then:
alter table store add constraint fk1
FOREIGN KEY (store_product) REFERENCES product (id)
deferrable initially deferred;
alter table product add constraint fk2
FOREIGN KEY (store_id) REFERENCES store (id);
alter table product add constraint fk3
FOREIGN KEY (inventory_id) REFERENCES inventory (id);
alter table inventory add constraint fk4
FOREIGN KEY (product_id) REFERENCES product (id);
2. Inserting Data
When inserting data that depends on each other you'll need to decide which row in which table you want to insert first. That's why the example above includes DEFERRABLE INITIALLY DEFERRED in the first constraint.
This way you can insert in sequence:
- Begin the transaction.
- Insert into
store--fk1is not validated yet. - Insert into
inventory. Validatesfk4. - Insert into
product. Validatesfk2andfk3. - Commit the transaction. At this point
fk1will be finally validated.
How to script primary key constraints from existing database
Powershell and SMO are going to be your friends here:
$option_drop = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions;
$option_drop.ScriptDrops = $true;
"" > drop_primary_keys.sql
"" > create_primary_keys.sql
$server = new-object Microsoft.SqlServer.Management.Smo.Server ".";
$db = $server.Databases['AdventureWorks'];
foreach ($table in $db.Tables) {
foreach ($index in $table.Indexes) {
if ($index.IndexKeyType -eq "DriPrimaryKey") {
$index.Script( $option_drop ) >> drop_primary_keys.sql
$index.Script() >> create_primary_keys.sql
}
}
}
A couple of notes here:
- Running this script will nuke any existing files of the name "drop_primary_keys.sql" and "create_primary_keys.sql", so proceed with caution
- The script doesn't take into account any foreign keys since you said you already have a way to do that.
- You may have to tweak the ScriptingOptions object to fit your needs. Specifically, I'm using the defaults on the create, so you may need to create another ScriptingOptions object and set whichever options you think appropriate.
Other than that, good hunting.
How to: Script Out Unique Constraint creation script
You can try something like this:
SELECT
ColumnName = c.name,
TableName = t.name,
CreateCmd = 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + ' ADD CONSTRAINT ' + i.name + ' UNIQUE(' + c.name + ')'
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN
sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
INNER JOIN
sys.tables t ON c.object_id = t.object_id
WHERE
is_unique_constraint = 1
This gives you a listing of columns and tables where a unique constraint is applied to, and it gives you the T-SQL command to newly create that UNIQUE CONSTRAINT
Multiple constraints in table: How to get all violations?
In the meantime I found a lean solution using deferred constraints:
CREATE TABLE A_TABLE_TEST (
COL_1 NUMBER NOT NULL DEFERRABLE INITIALLY DEFERRED,
COL_2 NUMBER NOT NULL DEFERRABLE INITIALLY DEFERRED,
COL_3 NUMBER NOT NULL DEFERRABLE INITIALLY DEFERRED,
COL_4 NUMBER NOT NULL DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO A_TABLE_TEST values (1,null,null,2);
DECLARE
CHECK_CONSTRAINT_VIOLATED EXCEPTION;
PRAGMA EXCEPTION_INIT(CHECK_CONSTRAINT_VIOLATED, -2290);
REF_CONSTRAINT_VIOLATED EXCEPTION;
PRAGMA EXCEPTION_INIT(REF_CONSTRAINT_VIOLATED , -2292);
CURSOR CheckConstraints IS
SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS USING (TABLE_NAME, CONSTRAINT_NAME)
WHERE TABLE_NAME = 'A_TABLE_TEST'
AND DEFERRED = 'DEFERRED'
AND STATUS = 'ENABLED';
BEGIN
FOR aCon IN CheckConstraints LOOP
BEGIN
EXECUTE IMMEDIATE 'SET CONSTRAINT '||aCon.CONSTRAINT_NAME||' IMMEDIATE';
EXCEPTION
WHEN CHECK_CONSTRAINT_VIOLATED OR REF_CONSTRAINT_VIOLATED THEN
DBMS_OUTPUT.PUT_LINE('Constraint '||aCon.CONSTRAINT_NAME||' at Column '||aCon.COLUMN_NAME||' violated');
END;
END LOOP;
END;
It works with any check constraint (not only NOT NULL). Checking FOREIGN KEY Constraint should work as well.
Add/Modify/Delete of constraints does not require any further maintenance.
Related Topics
Sql Primary Key, Int or Guid Or..
Round Date to 10 Minutes Interval
Replacing Variable Length String with Some Word
Get The Type of a Variable in MySQL
Query to Check Overlapping Ranges in SQL Server
Sql Design Approach for Searching a Table with an Unlimited Number of Bit Fields
Generate Create Scripts for a List of Indexes
What to Do When I Want to Use Database Constraints But Only Mark as Deleted Instead of Deleting
Sql Dynamic Order by Using Alias
Merge Two Tables/Concatenate Values into Single Column
I Am Trying to Copy a File, But Getting Error Message
How to Pass Schema as Parameter to a Stored Procedure in SQL Server
Sql Server: Table Variable Used in a Inner Join
The Identifier That Starts with ...... Is Too Long. Maximum Length Is 128
What Is The Advantage of Using Fast_Forward for Defining a Cursor
Sql Server Stored Procedure and Execute in VB.NET