T-SQL to Copy a Table From One DB to Another WITH All Triggers, Keys, Constraints

Question

T-SQL to Copy a Table From One DB to Another WITH All Triggers, Keys, Constraints

Rich Mechaber

SSChampion

Points: 11130
More actions
August 14, 2009 at 8:17 am

#213469

I occasionally create a few tables in a development DB and then want to move them to a production DB. Scripting the CREATE TABLE text doesn't copy all the DDL for the table, though.
Is there an easy way to do this? Will I have to go to sys views and get all the CREATE TRIGGER etc. text and build a new table from there? Seems like there ought to be an easier way to capture all of the data used to create a table. (Understanding that I'll have to manually inspect to make sure I don't have PK-FK violations.)
Thanks very much,
Rich

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

pretty sure the SSMS can do this for you; it's just some check marks int he scripting options to include triggers, constriants, etc.(TOOLS>>Options>>Scripting>> section for TABLE...check/uncheck various scripting options)

i contributed a script that does this all in TSQL; see if this helps...the latest version is in my signature for the link for "CREATE TABLE"; that one has some changes that also grab the extended properties(if any) that are assigned to the table.

http://www.sqlservercentral.com/scripts/SQL+Server+2005/67515/

you just call exec sp_GetDDL YourTableName, and it produces the complete DDL, triggers,rules,all default/check constraints, primiary keys, seperate indexes, you name it.

here's an example of the results for really complex table:

CREATE TABLE [dbo].[WHATEVER] (

[WHATEVERID] INT IDENTITY(2,5) NOT NULL,

[DESCRIP] VARCHAR(30) NULL,

[MYVARBINARY] VARBINARY NULL,

[MYBINARY] BINARY NULL

CONSTRAINT [DF__WHATEVER__mybina__5CD6CB2B] DEFAULT ((42)),

[MYIMAGE] IMAGE NULL,

[MYVARCHAR] VARCHAR(1) NULL,

[MYMAXVARCHAR] VARCHAR(max) NULL,

[MYCHAR] CHAR(1) NULL

CONSTRAINT [DF__WHATEVER__mychar__5DCAEF64] DEFAULT ('Y'),

[MYNVARCHAR] NVARCHAR(0) NULL,

[MYNCHAR] NCHAR(0) NULL,

[MYTEXT] TEXT NULL,

[MYNTEXT] NTEXT NULL,

[MYUNIQUEIDENTIFIER] UNIQUEIDENTIFIER NULL,

[MYROWVERSION] TIMESTAMP NOT NULL,

[MYBIT] BIT NULL,

[MYTINYINT] TINYINT NULL,

[MYSMALLINT] SMALLINT NULL,

[MYINT] INT NULL,

[MYBIGINT] BIGINT NULL,

[MYSMALLMONEY] SMALLMONEY NULL,

[MYMONEY] MONEY NULL,

[MYNUMERIC] NUMERIC(18,0) NULL,

[MYDECIMAL] DECIMAL(18,0) NOT NULL

CONSTRAINT [DF__WHATEVER__mydeci__5FB337D6] DEFAULT ((0)),

[MYREAL] REAL(24) NULL,

[MYFLOAT] FLOAT NULL,

[MYSMALLDATETIME] SMALLDATETIME NULL,

[MYDATETIME] DATETIME NULL,

[MYCALCULATEDCOLUMN] AS MYCALCULATEDCOLUMN,

[MYSQL_VARIANT] SQL_VARIANT NULL,

[MYXML] XML NULL,

[INSERTDT] DATETIME NULL

CONSTRAINT [DF__WHATEVER__INSERT__60A75C0F] DEFAULT (getdate()),

[UPDATEDDT] DATETIME NULL

CONSTRAINT [DF__WHATEVER__UPDATE__619B8048] DEFAULT (getdate()),

CONSTRAINT [PK__WHATEVER__5AEE82B9] PRIMARY KEY CLUSTERED (WHATEVERID),

CONSTRAINT [UQ__WHATEVER__5BE2A6F2] UNIQUE NONCLUSTERED (DESCRIP),

CONSTRAINT [FK__WHATEVER__myint__5EBF139D] FOREIGN KEY (myint) REFERENCES WHATEVERREF(WHATEVERREFID))

CREATE INDEX [IX_ANOTHERWHATEVER] ON [WHATEVER] (mytinyint, myvarchar)

CREATE INDEX [IX_WHATEVER] ON [WHATEVER] (mydatetime, myvarchar)

if not exists(SELECT [name] FROM sys.objects WHERE TYPE='R' AND schema_id = 1 AND [name] = '[range_rule]')

CREATE RULE range_rule

AS

@range>= $1000 AND @range <$20000;

GO

EXEC sp_binderule [WHATEVER], '[WHATEVER].[mymoney]'

GO

CREATE TRIGGER TR_WHATEVER

ON WHATEVER

FOR INSERT,UPDATE

AS

UPDATE WHATEVER

SET UPDATEDDT = GETDATE()

FROM INSERTED

WHERE WHATEVER.WHATEVERID=INSERTED.WHATEVERID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rich Mechaber SSChampion Points: 11130 More actions · Answer 2

Lowell, that's fantastic, thanks!

As for the GUI options, turns out those options aren't available in SSMS Express, which is what's installed on my desktop, only in the full version. Looks like a good reason to install the Enterprise SSMS on my desktop.

Very much appreciate your rapid answer and solutions!!

Rich

Lowell SSC Guru Points: 323518 More actions · Answer 3

thanks!

i think the script is pretty kewl, but there's so many ways to skin a cat...you can do the same in SMO and stuff, so not too many people want to do it in TSQL.

I keep tweaking it when i have time, it has really come a long way since the original SQL 200 cursor based version.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok i know i have SQL Express with Advanced Options instealled...i certainly have the options i was describing: some i've turned off, some are turned on...you can decide which to choose.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

T-SQL to Copy a Table From One DB to Another WITH All Triggers, Keys, Constraints

Cookies on SQLServerCentral