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.
| 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/
|
|
|
|
|
|
|
Generate sql script of a table schema in SQL 2000