Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A little Help Expand / Collapse
Export table Script
Poll ResultsVotes
Base on the code bellow, it actually works but the fact is that it does not show the complete create statement. if there anything any can do to help, I will be very grateful. The code is as follows
0%
0
0%
0
Member Votes: 0, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Monday, October 11, 2010 6:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 PM
Points: 15, Visits: 91
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_name varchar(32)
as
begin

Create Table #CreateStatements (uid int identity(1,1),Info text)

DECLARE @table_id int,
@CurrColumn int,
@MaxColumn int,
@CreateStatement varchar(8000),
@ColumnTypeName varchar(255),
@uid int,
@i int,
@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 @cursorID int
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
from sysindexes
where name = 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
Post #1002102
Posted Monday, October 11, 2010 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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:
Get any Tables DDL via TSQL


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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1002176
Posted Wednesday, October 13, 2010 1:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 PM
Points: 15, Visits: 91
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'.

Post #1003401
Posted Wednesday, October 13, 2010 3:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1003444
Posted Wednesday, October 20, 2010 2:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 PM
Points: 15, Visits: 91
Thanks, the code works but it truncates the result of must of my tables. Is there anything you can do?
Post #1007527
Posted Wednesday, October 20, 2010 5:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1007627
Posted Thursday, October 21, 2010 10:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 PM
Points: 15, Visits: 91

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.
Post #1008656
Posted Thursday, October 21, 2010 11:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1008671
Posted Thursday, October 28, 2010 6:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 PM
Points: 15, Visits: 91
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
Post #1012238
Posted Thursday, October 28, 2010 6:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1012245
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse