March 16, 2012 at 9:17 am
Hi
Any can help me for Generating the create and Drop script for Constraints and Indexes on a database
March 16, 2012 at 10:59 am
There is a DBScript utility (not sure if it's 100% compatible with SQL versions beyond 2005) but if you run it from a server with SQL 2005 installed on it I know it will run - it will create the create/drop scripts for any clustered/non-clustered index, FK's, and Triggers
You can check it out here - http://sqlsrvscrpt.codeplex.com/wikipage?title=DBScript%20Utility
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 16, 2012 at 11:05 am
For a particular constraint, locate and select it in the object explorer, SSMS. Right click and select script constraint as drop and create.
Unfortunately, that does it for only one object in the database.
John Miner
Crafty DBA
www.craftydba.com
March 16, 2012 at 11:15 am
The quickest way is to select the database name in the object explorer in SSMS.
Right Click.
Under the task menu, there is a generate script select.
It will bring up a bunch of objects that you can select and a place to store the results
John Miner
Crafty DBA
www.craftydba.com
March 16, 2012 at 11:16 am
Here is the URL to msdn with details.
http://msdn.microsoft.com/en-us/library/ms178078.aspx
John Miner
Crafty DBA
www.craftydba.com
May 24, 2012 at 5:09 am
Hi John,
very good article, but i found problem I cant solve. Hope You may help. Once I generate Create script for table a CREATE table contains CONSTRAINT definition, while I much prefer to generate instead ALTER TABLE ... ADD CONSTRAINT. Reason is I need to drop all constraints and indexes of all tables in database. Run loop to alter all columns with collation to another we need to use and than recreate Constraints and indexes on newly altered columns. But I have to change first CREATE TABLE ... CONSTRAINT to ALTER TABLE .... ADD CONSTRAINT. I got tens of tables in database, it is slightly complicated to change generated script with no errors.
Is there the solution how to generate all CONSTRAINTS in database like ALTER TABLE ADD ... because my tables are still exist?
Sincerely,
Ondrej
j.miner (3/16/2012)
Here is the URL to msdn with details.
May 24, 2012 at 5:54 am
Here is a script i use for indexes - you can modify it to your needs.
Press Alt>Q>S to specify the values for template parameters
/*
Author:<Author Name, sysname, Author Name>
Date created:<Data Created, sysname, dd Mmm YYYY>
Purpose:<Purpose, sysname, Purpose of this index>
Fillfactor:<Fillfactor, sysname, Default 90% for Transactional, 100% for Reference>
--Modifications History--
Changed OnChanged ByDefectChange Description
*/
IF OBJECT_ID(N'[<Schema Name, sysname, Schema Name>].[<Table Name, sysname, Table Name>]', N'U') IS NOT NULL
BEGIN
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[<Schema Name, sysname, Schema Name>].[<Table Name, sysname, Table Name>]', N'U')
AND [name] = N'NC_<Table Name, sysname, Table Name>_<Column Name, sysname, Column Name>')
BEGIN
CREATE NONCLUSTERED INDEX [NC_<Table Name, sysname, Table Name>_<Column Name, sysname, Column Name>] ON [<Schema Name, sysname, Schema Name>].[<Table Name, sysname, Table Name>](
[<Column Name, sysname, Column Name>] ASC) WITH (IGNORE_DUP_KEY = OFF, FILLFACTOR = <Fillfactor percentage, sysname, 90 for transactional, 100 for reference>)
IF EXISTS(SELECT 1 FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[<Schema Name, sysname, Schema Name>].[<Table Name, sysname, Table Name>]', N'U')
AND [name] = N'NC_<Table Name, sysname, Table Name>_<Column Name, sysname, Column Name>')
BEGIN
PRINT 'SUCCESS - Index [NC_<Table Name, sysname, Table Name>_<Column Name, sysname, Column Name>] created.'
END
ELSE
BEGIN
RAISERROR('FAIL - Index [NC_<Table Name, sysname, Table Name>_<Column Name, sysname, Column Name>] not created.',16,1)
END
END
ELSE
BEGIN
PRINT 'EXISTS - Index [NC_<Table Name, sysname, Table Name>_<Column Name, sysname, Column Name>] already exists.'
END
END
ELSE
BEGIN
RAISERROR('FAIL - <Table Name, sysname, Table Name> [<Schema Name, sysname, Schema Name>].[<Table Name, sysname, Table Name>] does not exist.',16,1)
END
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply