InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 18:16:51 GMT20http://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspxit's in the comments of hte proc:[code]--############################################################################# --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'--############################################################################# [/code]for SQL 2000, i'm not sure that proc exists...you can do it this way also:[code]--Turn system object marking onEXEC master.dbo.sp_MS_upd_sysobj_category 1GOCREATE PROCEDURE sp_GETDDL2 AS ....GO--Turn system object marking offEXEC master.dbo.sp_MS_upd_sysobj_category 2[/code]Thu, 28 Oct 2010 06:18:11 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspxThanks, 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 outThu, 28 Oct 2010 06:04:25 GMTleaders_jhttp://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspxi 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 [b]INSERT INTO #Results [/b]--> [b]INSERT INTO #Results[color="FF0000"]([sql])[/color][/b]Thu, 21 Oct 2010 11:01:34 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspxI tried the code and I got the following errorsServer: Msg 8101, Level 16, State 1, Procedure sp_GetDDL2, Line 78An 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 344An 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 400An explicit value for the identity column in table '#Results' can only be specified when a column list is used and IDENTITY_INSERT is ON.Thu, 21 Oct 2010 10:33:18 GMTleaders_jhttp://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspx[quote][b]leaders_j (10/20/2010)[/b][hr]Thanks, the code works but it truncates the result of must of my tables. Is there anything you can do?[/quote]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 [b]have [/b]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 [b]table[/b]...basically each column as it's own row:[b][url=http://www.stormrage.com/Portals/0/SSC/sp_GetDDL2000_V3.txt]Get DDL for Any SQL 2000 Table Version 3[/url][/b]Wed, 20 Oct 2010 05:42:57 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspxThanks, the code works but it truncates the result of must of my tables. Is there anything you can do?Wed, 20 Oct 2010 02:42:35 GMTleaders_jhttp://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspxyeah, 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:[b][url=http://www.sqlservercentral.com/scripts/SQL+Server+7%2c+2000/67516/]Get DDL for any SQL 2000 table[/url][/b]Wed, 13 Oct 2010 03:43:23 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspxThanks 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 40Line 40: Incorrect syntax near 'max'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 55Must 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 71Must declare the variable '@FINALSQL'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 71Must declare the variable '@FINALSQL'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 79Must declare the variable '@FINALSQL'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 89Must declare the variable '@FINALSQL'.Server: Msg 170, Level 15, State 1, Procedure sp_GetDDL, Line 312Line 312: Incorrect syntax near 'max'.Server: Msg 170, Level 15, State 1, Procedure sp_GetDDL, Line 336Line 336: Incorrect syntax near 'APPLY'.Server: Msg 156, Level 15, State 1, Procedure sp_GetDDL, Line 353Incorrect syntax near the keyword 'FOR'.Server: Msg 156, Level 15, State 1, Procedure sp_GetDDL, Line 364Incorrect syntax near the keyword 'ORDER'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 385Must declare the variable '@CONSTRAINTSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 390Must declare the variable '@INDEXSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 390Must declare the variable '@CONSTRAINTSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 434Must declare the variable '@INDEXSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 461Must declare the variable '@INDEXSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 462Must declare the variable '@vbCrLf'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 467Must declare the variable '@CHECKCONSTSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 468Must declare the variable '@CHECKCONSTSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 481Must declare the variable '@FKSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 483Must declare the variable '@FKSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 496Must declare the variable '@RULESCONSTSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 497Must declare the variable '@RULESCONSTSQLS'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 514Must declare the variable '@TRIGGERSTATEMENT'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 515Must declare the variable '@TRIGGERSTATEMENT'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 522Must declare the variable '@TRIGGERSTATEMENT'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 523Must declare the variable '@vbCrLf'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 528Must declare the variable '@EXTENDEDPROPERTIES'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 529Must declare the variable '@EXTENDEDPROPERTIES'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 536Must declare the variable '@EXTENDEDPROPERTIES'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 537Must declare the variable '@vbCrLf'.Server: Msg 137, Level 15, State 2, Procedure sp_GetDDL, Line 543Must declare the variable '@FINALSQL'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 548Must declare the variable '@FINALSQL'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 549Must declare the variable '@FINALSQL'.Server: Msg 137, Level 15, State 1, Procedure sp_GetDDL, Line 552Must declare the variable '@FINALSQL'.Wed, 13 Oct 2010 01:59:40 GMTleaders_jhttp://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspxleaders_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:[b][url=http://www.stormrage.com/Portals/0/SSC/sp_GetDDL2005_V307.txt] Get any Tables DDL via TSQL[/url][/b]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.Mon, 11 Oct 2010 07:47:45 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1002102-566-1.aspxBase 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 followsCREATE PROCEDURE sp_ExportTables -- USAGE: sp_ExportTables gmproj@table_name varchar(32)as beginCreate 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 @CreateStatementSelect info from #CreateStatementsdrop table #CreateStatementsendMon, 11 Oct 2010 06:20:58 GMTleaders_j