Table Copy to Temp Table with Constraints

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Table Copy to Temp Table with Constraints

Author

Topic

akwoolf
Starting Member

3 Posts

Posted - 2011-10-28 : 23:12:47

Hello-

I am attempting to make a copy of a SQL table with all constraints and properties, BUT without data. I generated the script below from the SQL Management Console. (SQL 2005) I need assistance determining what to do with the script now that I have generated it... I would like to create a new table called "StatusMessages2" - Any help is much appreciated.
(Script generated from a table called "StatusMessages_old")

********

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StatusMessages_old]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StatusMessages_old](
[RecordID] [int] NOT NULL,
[ModuleName] [varchar](128) NOT NULL,
[ID] [int] NOT NULL,
[Win32Error] [int] NULL,
[Time] [datetime] NOT NULL,
[SiteCode] [varchar](3) NOT NULL,
[TopLevelSiteCode] [varchar](3) NULL,
[MachineName] [varchar](128) NOT NULL,
[Component] [varchar](128) NOT NULL,
[ProcessID] [int] NULL,
[ThreadID] [int] NULL,
[Type] [int] NOT NULL,
[DeleteTime] [datetime] NULL,
CONSTRAINT [StatusMessages_PK] PRIMARY KEY NONCLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[StatusMessages_del]'))
EXEC dbo.sp_executesql @statement = N' CREATE TRIGGER [dbo].[StatusMessages_del] on [dbo].[StatusMessages_old] for delete AS BEGIN Set RowCount 0 delete StatusMessageAttributes from StatusMessageAttributes INNER JOIN deleted ON StatusMessageAttributes.RecordID = deleted.RecordID delete StatusMessageInsStrs from StatusMessageInsStrs INNER JOIN deleted ON StatusMessageInsStrs.RecordID = deleted.RecordID END '

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 03:15:38

just replace all occurances of StatusMessages_old with new table name StatusMessages2 and also trigger name from StatusMessages_del to something like StatusMessages2_del_trig and then run the script in new window

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

akwoolf
Starting Member

3 Posts

Posted - 2011-10-30 : 11:28:12

Thank you for the response. Will this copy the contents of the existing table or just create a new table with all constraints and other properties?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 11:31:05

this will create new table with constraints and other properties but will not fill any data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

akwoolf
Starting Member

3 Posts

Posted - 2011-10-30 : 11:56:36

Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 12:02:03

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources