Generate sql script of a table schema in SQL 2000

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 2000 Forums

Transact-SQL (2000)

Generate sql script of a table schema in SQL 2000

Author

Topic

ssa2010
Starting Member

4 Posts

Posted - 2012-05-19 : 06:10:03

I wrote some scripts for correcting my old sql 2000 db schema compared to new sql 2000 db. This is done through a loop operations. First check if any table not found in my old db, then running a table creation script for creating that table. If any system stored procedures or scripts availble for table creation ?. I want table scripts like when we take 'generate sql script' process.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-19 : 15:49:13

what do you mean by
I want table scripts like when we take 'generate sql script' process.

do you mean by


IF EXISTS()
 DROP
GO

CREATE
...
GO

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

ssa2010
Starting Member

4 Posts

Posted - 2012-05-20 : 02:55:04

Hi visakh16

Thanks for ur reply.

Yes, I mean that I want table scripts like

IF EXISTS()
DROP
GO

CREATE
...
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-20 : 10:59:17

quote:
Originally posted by ssa2010

Hi visakh16

Thanks for ur reply.

Yes, I mean that I want table scripts like

IF EXISTS()
DROP
GO

CREATE
...
GO

you can generate this programatically if you want

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

ssa2010
Starting Member

4 Posts

Posted - 2012-05-21 : 00:31:36

How can I generate this programmatically ?

ssa2010

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 21:37:24

quote:
Originally posted by ssa2010

How can I generate this programmatically ?

ssa2010

using syscomments system table

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

ssa2010
Starting Member

4 Posts

Posted - 2012-05-22 : 01:05:21

What data contains in syscomments system table ?

ssa2010

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 14:40:54

the actual body of proc

use something like

CREATE PROC GenerateProcCode
@ProcName varchar(1000)
AS
DECLARE @Body varchar(8000)

SELECT @Body = COALESCE(@Body,'') + text
FROM syscomments
WHERE OBJECT_NAME(id) = @ProcName
SELECT 'IF EXISTS(SELECT 1 FROM sysobjects WHERE type=''p'' AND name = ''' + @ProcName + ''') DROP PROC ' + @ProcName +' GO ' + @Body
GO
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources