August 19, 2005 at 7:40 am
OK I've built my own function where if you pass it the tablename, it generates the DDL statement to recreate the table complete with constraints,defaults,foreign keys, etc, but in a standard format; a sister query also throws out the same table for ORACLE creating SEQUENCES and a table trigger to emulate the IDENTITY; it works great ,but i took a lazy shortcut on the IDENTITY definition..hardcodeded the column as IDENTITY(1,1) if the syscolumns.autoval is not null for the column definition, and i want to improve it so it gets the actual definition.
as an example:
drop table test3
create table test1 (
incBy1 int identity(1,1) )
create table test2 (
incBy2 int identity(1,2) )
create table test3 (
StartBy2 int identity(2,3) )
select name,autoval from syscolumns where id in(select id from sysobjects where name in('test1','test2','test3'))
incBy1 0x01000000010000000100000003
incBy2 0x01000000020000000100000003
StartBy2 0x02000000030000000200000003
hopefully it is obvious that the autoval column has parts of it's value that relate to the "start at"(in red) and incrementby(in black) values in it, but i'm not sure how to extract those two values from the varbinary field.
here's an example:
select name + ' '
+ TYPE_NAME(xtype) + ' '
+ CASE WHEN isnullable=0 THEN ' NOT NULL' ELSE ' NULL'END
+ CASE WHEN autoval IS NULL THEN ' ' ELSE ' IDENTITY(1,1)' END
from syscolumns where id in(select id from sysobjects where name in('test1','test2','test3'))
results:
incBy1 int NOT NULL IDENTITY(1,1)
incBy2 int NOT NULL IDENTITY(1,1)
StartBy2 int NOT NULL IDENTITY(1,1)
but really i need to beef up the else statement for the autoval, but I'm stuck as to how to extract the vales.
any ideas?
Lowell
August 19, 2005 at 8:07 am
I haven't found the actual code to find the info from the column, but this will work just as well :
Select ident_seed('TableName') as Seed, IDENT_INCR('TableName') as Increment, IDENT_CURRENT('TableName') as CurrentId
August 19, 2005 at 10:42 am
Thanks Remi; that got me over the hump; here's an example of a pretty complex table that my function returns the ddl as an example:
it still needs some work, because if you have a table definition that is greater than 8000, it gets truncated, so i might need to get this to return rows of data as a stored procedure instead of a single value function; i'll keep playing with it. the big thing is to get it formatted,
I use it in a proc that gets the foreign key hierarchy order so the tables are in the right order as far as FK's go.
SELECT dbo.fn__tbsql( 'GMHOPBEN2',null)
CREATE TABLE [GMHOPBEN2] (
[HOPBENTBLKEY] INT IDENTITY(150,2) NOT NULL,
[ACTTBLKEY] INT NOT NULL,
[BENNAME] VARCHAR (50) NULL,
[SSN] VARCHAR (9) NULL,
[ADDR1] VARCHAR (40) NULL,
[ADDR2] VARCHAR (40) NULL,
[CITY] VARCHAR (20) NULL,
[STATE] VARCHAR (2) NULL,
[ZIPCODE] VARCHAR (9) NULL,
[DOB] DATETIME NULL,
[RACETBLKEY] INT NULL,
[ETHNICTBLKEY] INT NULL,
[GENDER] CHAR (1) NULL CHECK ([GENDER] = 'F' or [GENDER] = 'M') ,
[GROSSINCOME] MONEY NOT NULL DEFAULT (0),
[HOUSINGPAYMENT] MONEY NOT NULL DEFAULT (0),
[PROGENTERDT] DATETIME NULL,
[PROGLEAVEDT] DATETIME NULL,
[ACTAREATBLKEY] INT NULL,
[HOPLIVINGTBLKEY] INT NULL,
[DEPARTURETBLKEY] INT NULL,
[SSTYPETBLKEY] INT NULL,
[FACILITYTBLKEY] INT NULL,
[BEDCODTBLKEY] INT NULL,
[RCVSUPPORTSVC] CHAR (1) NOT NULL DEFAULT ('N') CHECK ([RCVSUPPORTSVC] = 'N' or [RCVSUPPORTSVC] = 'Y') ,
[RCVHOUSINGAST] CHAR (1) NOT NULL DEFAULT ('N') CHECK ([RCVHOUSINGAST] = 'N' or [RCVHOUSINGAST] = 'Y') ,
[RCVLONGTERM] CHAR (1) NOT NULL DEFAULT ('N') CHECK ([RCVLONGTERM] = 'N' or [RCVLONGTERM] = 'Y') ,
[RCVSHORTTERM] CHAR (1) NOT NULL DEFAULT ('N') CHECK ([RCVSHORTTERM] = 'N' or [RCVSHORTTERM] = 'Y') ,
[TOTHHSIZE] INT NOT NULL DEFAULT (1),
[TOTUNDER18] INT NOT NULL DEFAULT (0),
CONSTRAINT [PK__GMHOPBEN2__13A8A87F] PRIMARY KEY ([HOPBENTBLKEY]),
FOREIGN KEY (ACTTBLKEY) REFERENCES GMACT(ACTTBLKEY),
FOREIGN KEY (RACETBLKEY) REFERENCES TBRACE(RACETBLKEY),
FOREIGN KEY (ETHNICTBLKEY) REFERENCES TBETHNIC(ETHNICTBLKEY),
FOREIGN KEY (ACTAREATBLKEY) REFERENCES TBACTA(ACTAREATBLKEY),
FOREIGN KEY (HOPLIVINGTBLKEY) REFERENCES TBHOPLIVING(HOPLIVINGTBLKEY),
FOREIGN KEY (DEPARTURETBLKEY) REFERENCES TBDEPART(DEPARTURETBLKEY),
FOREIGN KEY (SSTYPETBLKEY) REFERENCES TBSSTYPE(SSTYPETBLKEY),
FOREIGN KEY (FACILITYTBLKEY) REFERENCES TBFACIL(FACILITYTBLKEY),
FOREIGN KEY (BEDCODTBLKEY) REFERENCES TBBEDCOD(BEDCODTBLKEY))
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply