October 11, 2010 at 6:20 am
Base on the code bellow, it actually works but the fact is that it does not show the complete create statement. if there anything you can do to help, I will be very grateful. The code is as follows
CREATE PROCEDURE sp_ExportTables
-- USAGE: sp_ExportTables gmproj
@table_namevarchar(32)
as
begin
Create Table #CreateStatements(uid int identity(1,1),Infotext)
DECLARE @table_id int,
@CurrColumnint,
@MaxColumnint,
@CreateStatementvarchar(8000),
@ColumnTypeNamevarchar(255),
@uidint,
@iint,
@primary_key_field varchar(50)
select @table_id=id from sysobjects where xtype='U' and [name] <> 'dtproperties' and [name] = @table_name
/*Since a table can have only one Primary key, get the column name for this table(if any) that is the PK*/
select @primary_key_field = convert(varchar(32),c.name)
from
sysindexes i, syscolumns c, sysobjects o, syscolumns c1
where
o.id = @table_id
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
and c.name = index_col (@table_name, i.indid, c1.colid)
and c1.colid <= i.keycnt
and c1.id = @table_id
Select @CreateStatement = CHAR(13) + 'CREATE TABLE [' + [name] + '] ( ' from SYSOBJECTS WHERE ID=@TABLE_ID
--For Each Column
Select @CurrColumn=Min(colid),@MaxColumn = Max(colid) from syscolumns where id= @table_id
--Select * from syscolumns where id=1511676433
while @currColumn <= @MaxColumn
begin
--print @currColumn
Declare @UQIndex int, @DefaultValue nvarchar(4000)
set @DefaultValue = null
select @DefaultValue=text from syscomments where id=
(select constid from sysconstraints where id=@table_id and colid=@currColumn)
--Process different Column Types differently
SELECT @CreateStatement = @CreateStatement + CHAR(13) + '[' + [name] + '] ' + type_name(xtype) +
case
--ie numeric(10,2)
WHEN type_name(xtype) IN ('decimal','numeric') THEN
' ('+ convert(varchar,prec) + ',' + convert(varchar,length) + ')'
+ case when autoval is null then '' else ' IDENTITY(1,1)' end
+ CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END
--ie float(53)
WHEN type_name(xtype) IN ('float','real') THEN
' ('+ convert(varchar,prec) + ')'
+ case when autoval is null then '' else ' IDENTITY(1,1)' end
+ CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END
--ie varchar(40)
WHEN type_name(xtype) IN ('char','varchar','nchar','nvarchar') THEN
' ('+ convert(varchar,length) + ')'
+ case when autoval is null then '' else ' IDENTITY(1,1)' end
+ CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END
--ie int
ELSE
+ case when autoval is null then '' else ' IDENTITY(1,1)' end
+ CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END
end
--code to determine if 'PRIMARY KEY'
+ CASE when syscolumns.name = @primary_key_field THEN ' PRIMARY KEY' else '' END
+ CASE when @DefaultValue is null then ''
ELSE
CASE
WHEN type_name(xtype) IN ('decimal','numeric','float','real','bigint','int','smallint','tinyint','money','smallmoney') THEN
' DEFAULT ' + convert(varchar,@DefaultValue)
ELSE
' DEFAULT ' + convert(varchar,@DefaultValue)
END
END
+ ',' from syscolumns where id=@table_id and colid=@CurrColumn
Select @CurrColumn = @CurrColumn + 1
end
insert into #CreateStatements(Info) values(@CreateStatement)
SELECT @CreateStatement=''
select @uid=@@IDENTITY
--CODE TO ADD ALL THE FOREIGN KEYS TO THE BOTTOM OF THE STATEMENT
declare @cursorIDint
declare c1 cursor for SELECT fkeyid from sysforeignkeys where fkeyid=@table_id
open c1
fetch next from c1 into @cursorID
SELECT @CreateStatement=@CreateStatement +
(select + CHAR(13) +'FOREIGN KEY (' + [syscolumns].[name] + ') REFERENCES ' from syscolumns where id=fkeyid and colid =fkey) +
(select (SELECT distinct [sysobjects].[name] from sysobjects where id=rkeyid) + '(' + [syscolumns].[name] + '),' from syscolumns where id=rkeyid and colid =rkey)
from sysforeignkeys where fkeyid=@table_id
close c1
deallocate c1
--CODE TO ADD ALL THE UNIQUE CONSTRAINTS TO THE BOTTOM OF THE DEFINITION.
declare c1 cursor for select id from sysobjects where xtype='UQ' and parent_obj=@table_id
open c1
fetch next from c1 into @cursorID
--adapted shamelessly from sp_helpconstraints
while @@fetch_status >= 0
begin
declare @indid smallint
SELECT @indid = indid,@CreateStatement=@CreateStatement + CHAR(13) + 'CONSTRAINT ' + object_name(@cursorID) + ' UNIQUE '
+ case when (status & 16)=16 then ' CLUSTERED' else ' NONCLUSTERED' end
fromsysindexes
wherename = object_name(@cursorID) and id = @table_ID
declare @thiskey nvarchar(131), -- 128+3
@keys nvarchar(2126) -- a specific size for MS for whatever reason
select @keys = index_col(@table_name, @indid, 1), @i = 2
if (indexkey_property(@table_id, @indid, 1, 'isdescending') = 1)
select @keys = @keys + '(-)'
select @thiskey = index_col(@table_name, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@table_ID, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
while (@thiskey is not null)
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@table_name, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@table_ID, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end
Select @CreateStatement=@CreateStatement + '(' + @keys + '),'
fetch next from c1 into @cursorID
end
close c1
deallocate c1
--CODE TO ADD CHECK CONSTRAINTS TO THE BOTTOM OF THE DEFINITION?
--CODE TO ADD INDEXES TO THE BOTTOM OF THE DEFINITION?
--at this point, there is a trailing comma, or it blank
DECLARE @ptrval binary(16),@txtlen INT
if len(@CreateStatement) > 0
BEGIN
SELECT @ptrval = TEXTPTR(info) ,
@txtlen = DATALENGTH(info)
FROM #CreateStatements
WHERE uid=@uid
UPDATETEXT #CreateStatements.info @ptrval @txtlen 0 @CreateStatement
END
--note that this trims the trailing comma from the end of the statement
SELECT @ptrval = TEXTPTR(info) ,
@txtlen = DATALENGTH(info) - 1
FROM #CreateStatements
WHERE uid=@uid
SELECT @CreateStatement= ')'+ CHAR(13)
UPDATETEXT #CreateStatements.info @ptrval @txtlen 1 @CreateStatement
Select info from #CreateStatements
drop table #CreateStatements
end
October 11, 2010 at 7:47 am
leaders_j that code snippet you have is really old; i bet it's from 5 years ago.
It's from a project i play with now and then that scripts any table via TSQL.
are you trying to script the CREATE table statements?
i've got some really advanced versions of that code since i wrote that, that are no longer cursor based.
try this version for now:
my newest version (v309) scripts any object act all(procedure/function/table/view etc.
I should note that everything my script does, the GUI does, and probably better, but i love writing my own solutions.
Lowell
October 13, 2010 at 1:59 am
Thanks for the code anyway, But I still need a favour.
The SQL code you gave me has the following errors in Sql Server 2000. They include
Server: Msg 170, Level 15, State 1, Procedure sp_GetDDL, Line 40
Line 40: Incorrect syntax near 'max'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 55
Must declare the variable '@vbCrLf'.
Server: Msg 195, Level 15, State 1, Procedure sp_GetDDL, Line 63
'schema_id' is not a recognized function name.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 71
Must declare the variable '@FINALSQL'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 71
Must declare the variable '@FINALSQL'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 79
Must declare the variable '@FINALSQL'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 89
Must declare the variable '@FINALSQL'.
Server: Msg 170, Level 15, State 1, Procedure sp_GetDDL, Line 312
Line 312: Incorrect syntax near 'max'.
Server: Msg 170, Level 15, State 1, Procedure sp_GetDDL, Line 336
Line 336: Incorrect syntax near 'APPLY'.
Server: Msg 156, Level 15, State 1, Procedure sp_GetDDL, Line 353
Incorrect syntax near the keyword 'FOR'.
Server: Msg 156, Level 15, State 1, Procedure sp_GetDDL, Line 364
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 385
Must declare the variable '@CONSTRAINTSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 390
Must declare the variable '@INDEXSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 390
Must declare the variable '@CONSTRAINTSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 434
Must declare the variable '@INDEXSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 461
Must declare the variable '@INDEXSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 462
Must declare the variable '@vbCrLf'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 467
Must declare the variable '@CHECKCONSTSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 468
Must declare the variable '@CHECKCONSTSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 481
Must declare the variable '@FKSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 483
Must declare the variable '@FKSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 496
Must declare the variable '@RULESCONSTSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 497
Must declare the variable '@RULESCONSTSQLS'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 514
Must declare the variable '@TRIGGERSTATEMENT'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 515
Must declare the variable '@TRIGGERSTATEMENT'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 522
Must declare the variable '@TRIGGERSTATEMENT'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 523
Must declare the variable '@vbCrLf'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 528
Must declare the variable '@EXTENDEDPROPERTIES'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 529
Must declare the variable '@EXTENDEDPROPERTIES'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 536
Must declare the variable '@EXTENDEDPROPERTIES'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 537
Must declare the variable '@vbCrLf'.
Server: Msg 137, Level 15, State 2, Procedure sp_GetDDL, Line 543
Must declare the variable '@FINALSQL'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 548
Must declare the variable '@FINALSQL'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 549
Must declare the variable '@FINALSQL'.
Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 552
Must declare the variable '@FINALSQL'.
October 13, 2010 at 3:43 am
yeah, i switched away from SQL2000 a while ago, and all my scripts are targeted for the newer platform.
take a look at this script contribution i made for SQL 2000:
Get DDL for any SQL 2000 table[/url]
Lowell
October 20, 2010 at 2:42 am
Thanks, the code works but it truncates the result of must of my tables. Is there anything you can do?
October 20, 2010 at 5:42 am
leaders_j (10/20/2010)
Thanks, the code works but it truncates the result of must of my tables. Is there anything you can do?
since SQL 2000 is limited to varchar(8000), and you may have table definitions that are longer than that, what with the formatting that proc uses, you are limited if you have to have it in a single string. That version will give truncated results for long definitions.
if a multi row results are ok, you can try this version, which returns a table...basically each column as it's own row:
Get DDL for Any SQL 2000 Table Version 3
Lowell
October 21, 2010 at 10:33 am
I tried the code and I got the following errors
Server: Msg 8101, Level 16, State 1, Procedure sp_GetDDL2, Line 78
An explicit value for the identity column in table '#Results' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Procedure sp_GetDDL2, Line 344
An explicit value for the identity column in table '#Results' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Procedure sp_GetDDL2, Line 400
An explicit value for the identity column in table '#Results' can only be specified when a column list is used and IDENTITY_INSERT is ON.
October 21, 2010 at 11:01 am
i don't have a true SQL 2000 installation, and the code works flawlessly in SQL 80 compatibility mode.
try doing a find and replace for INSERT INTO #Results --> INSERT INTO #Results([sql])
Lowell
October 28, 2010 at 6:04 am
Thanks, you've really been helpfull. This time I can not script another table from another database. It give the message -"Table object [dbo].[CASE_CONFIRM] does not exist in Database [master]". I will be grateful if you help me out
October 28, 2010 at 6:18 am
it's in the comments of hte proc:
--#############################################################################
--if you are going to put this in MASTER, and want it to be able to query
--each database's sys.indexes, you MUST mark it as a system procedure:
--EXECUTE sp_ms_marksystemobject 'sp_GetDDL2'
--#############################################################################
for SQL 2000, i'm not sure that proc exists...you can do it this way also:
--Turn system object marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
GO
CREATE PROCEDURE sp_GETDDL2 AS ....
GO
--Turn system object marking off
EXEC master.dbo.sp_MS_upd_sysobj_category 2
Lowell
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply