T-SQL to Copy a Table From One DB to Another WITH All Triggers, Keys, Constraints
-
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
-
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
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! -
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
-
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! -
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!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply