A little Help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Lowell » A little Help

A little Help

Rate Topic

Display Mode

Topic Options

Export table Script

Poll Results

Votes

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

Member Votes: 0, Anonymous Votes: 0. You don't have permission to vote within this poll.

Author

Message

leaders_j

Posted Monday, October 11, 2010 6:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:30 AM
Points: 9, Visits: 72

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

Lowell

Posted Monday, October 11, 2010 7:47 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 11,792, Visits: 28,078

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

leaders_j

Posted Wednesday, October 13, 2010 1:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:30 AM
Points: 9, Visits: 72

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

Lowell

Posted Wednesday, October 13, 2010 3:43 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 11,792, Visits: 28,078

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

leaders_j

Posted Wednesday, October 20, 2010 2:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:30 AM
Points: 9, Visits: 72

Thanks, the code works but it truncates the result of must of my tables. Is there anything you can do?

Post #1007527

Lowell

Posted Wednesday, October 20, 2010 5:42 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 11,792, Visits: 28,078

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

leaders_j

Posted Thursday, October 21, 2010 10:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:30 AM
Points: 9, Visits: 72

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

Lowell

Posted Thursday, October 21, 2010 11:01 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 11,792, Visits: 28,078

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

leaders_j

Posted Thursday, October 28, 2010 6:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:30 AM
Points: 9, Visits: 72

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

Lowell

Posted Thursday, October 28, 2010 6:18 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 11,792, Visits: 28,078

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 »

Permissions