Alter proc to fetch a table structure from SQL Server 2005 database

60 pts.
Tags:
FETCH statement
SQL 2005
SQL 2005 Database
SQL Server 2005
SQL Server tables
hi please segest me... what is the problem in this proc. CREATE Procedure sp_tab @tablename nvarchar(517), @flags int = 0, @orderby nvarchar(10) = null, @flags2 int = 0 As if @flags is null select @flags = 0 if (@tablename = N'?') begin print N'' print N'Usage: sp_table_struct @tablename, @flags int = 0' print N' where @flags is a bitmask of:' print N' 0x0200 = No DRI (ignore Checks, Primary/Foreign/Unique Keys, etc.)' print N' 0x0400 = UDDTs --> Base type' print N' 0x80000 = TimestampToBinary (convert timestamp cols to binary(8))' print N' 0x40000000 = No Identity attribute' return 0 end declare @objid int select @objid = object_id(@tablename) if (@objid is null) begin RAISERROR (15001, -1, -1, @tablename) return 1 end set nocount on create table #sphelpcols ( col_name nvarchar(128) NOT NULL, col_id int NOT NULL, col_typename nvarchar(128) NOT NULL, col_len int NOT NULL, col_prec int NULL, col_scale int NULL, col_numtype smallint NOT NULL, /* For DaVinci to get sp_help-type filtering of prec/scale */ col_null bit NOT NULL, /* status & 8 */ col_identity bit NOT NULL, /* status & 128 */ col_defname nvarchar(257) NULL, /* fully-qual'd default name, or NULL */ col_rulname nvarchar(257) NULL, /* fully-qual'd rule name, or NULL */ col_basetypename nvarchar(128) NOT NULL, col_flags int NULL, /* COL_* bits */ col_seed numeric (28) NULL, col_increment int NULL, col_dridefname nvarchar(128) NULL, /* DRI DEFAULT name */ col_dridefid int NULL, /* remember the DRI DEFAULT id in syscomments, so we can retrieve it later */ col_iscomputed int NOT NULL, col_objectid int NOT NULL, /* column object id, need it to get computed text from syscomments */ col_NotForRepl bit NOT NULL, /* Not For Replication setting */ col_fulltext bit NOT NULL, /* FullTextIndex setting */ col_AnsiPad bit NULL, /* Ansi_Padding setting */ /* following columns are repeating the info from col_defname and col_rulname */ /* because we can not change data in col_defname and col_rulname, since daVinci is using them */ col_DOwner nvarchar(128) NULL, /* non-DRI DEFAULT owner, or NULL */ col_DName nvarchar(128) NULL, /* non-DRI DEFAULT name, or NULL */ col_ROwner nvarchar(128) NULL, /* non-DRI RULE owner, or NULL */ col_RName nvarchar(128) NULL, /* non-DRI RULE name, or NULL */ ) /* Do not store the computed text in this temp table, because one extra join causes big performance hit */ /* First load stuff so we can blot off inappropriate info and massage as per @flags */ insert #sphelpcols select c.name, c.colid, st.name, case when bt.name in (N'nchar', N'nvarchar') then c.length/2 else c.length end, ColumnProperty(@objid, c.name, N'Precision'), ColumnProperty(@objid, c.name, N'Scale'), -- col_numtype for DaVinci: use sp_help-type prec/scale filtering for @flags2 & 1 case when (@flags2 & 1 <> 0 and bt.name in (N'tinyint',N'smallint',N'decimal',N'int',N'real',N'money',N'float',N'numeric',N'smallmoney')) then 1 else 0 end, -- Nullable convert(bit, ColumnProperty(@objid, c.name, N'AllowsNull')), -- Identity case when (@flags & 0x40000000 = 0) then convert(bit, ColumnProperty(@objid, c.name, N'IsIdentity')) else 0 end, -- Non-DRI Default (make sure it's not a DRI constraint). case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else user_name(d.uid) + N'.' + d.name end, -- Non-DRI Rule case when (c.domain = 0) then null else user_name(r.uid) + N'.' + r.name end, -- Physical base datatype bt.name, -- Initialize flags to whether it's a length-specifiable type, or a numeric type, or 0. case when st.name in (N'char',N'varchar',N'binary',N'varbinary',N'nchar',N'nvarchar') then 0x0001 when st.name in (N'decimal',N'numeric') then 0x0002 else 0 end -- Will be NULL if column is not UniqueIdentifier. + case isnull(ColumnProperty(@objid, c.name, N'IsRowGuidCol'), 0) when 0 then 0 else 0x0008 end, -- Identity seed and increment case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_seed(@tablename) else null end, case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_incr(@tablename) else null end, -- DRI Default name case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0)) then object_name(c.cdefault) else null end, -- DRI Default text, if it does not span multiple rows (if it does, SQLDMO will go get them all). case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0)) then t.id else null end, c.iscomputed, c.id, -- Not For Replication convert(bit, ColumnProperty(@objid, c.name, N'IsIdNotForRepl')), convert(bit, ColumnProperty(@objid, c.name, N'IsFulltextIndexed')), convert(bit, ColumnProperty(@objid, c.name, N'UsesAnsiTrim')), -- Non-DRI Default owner and name case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else user_name(d.uid) end, case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else d.name end, -- Non-DRI Rule owner and name case when (c.domain = 0) then null else user_name(r.uid) end, case when (c.domain = 0) then null else r.name end from syscolumns c -- NonDRI Default and Rule filters left outer join sysobjects d on d.id = c.cdefault left outer join sysobjects r on r.id = c.domain -- Fully derived data type name join systypes st on st.xusertype = c.xusertype -- Physical base data type name join systypes bt on bt.xusertype = c.xtype -- DRIDefault text, if it's only one row. left outer join syscomments t on t.id = c.cdefault and t.colid = 1 and not exists (select * from syscomments where id = c.cdefault and colid = 2) Where c.id = @objid Order by c.colid /* Convert any timestamp column to binary(8) if they asked. */ if (@flags & 0x80000 != 0) update #sphelpcols set col_typename = N'binary', col_len = 8, col_flags = col_flags | 0x0001 where col_typename = N'timestamp' /* Now see what our flags are, if anything. */ if (@flags is not null and @flags != 0) begin if (@flags & 0x0400 != 0) begin /* Track from xusertype --> b.[Base][/base]xtype --> u.xusertype in systypes */ /* First mask off the things we will set. The convert() awkwardness is */ /* necessitated by SQLServer's handling of 0x-prefixed values. */ declare @typeflagmask int select @typeflagmask = (convert(int, 0x0001) + convert(int, 0x0002)) update #sphelpcols set col_typename = b.name, -- ReInitialize flags to whether it's a length-specifiable type, or a numeric type, or 0. col_flags = col_flags & ~@typeflagmask + case when b.name in (N'char',N'varchar',N'binary',N'varbinary',N'nchar',N'nvarchar') then 0x0001 when b.name in (N'decimal',N'numeric') then 0x0002 else 0 end from #sphelpcols c, systypes n, systypes b where n.name = col_typename --// xtype (base type) of name and b.xusertype = n.xtype --// Map it back to where it's xusertype, to get the name end end /* Determine if the column is in the primary key */ if (@flags & 0x0200 = 0 and (OBJECTPROPERTY(@objid, N'TableHasPrimaryKey') <> 0)) begin declare @indid int select @indid = indid from sysindexes i where i.id = @objid and i.status & 0x0800 <> 0 if (@indid is not null) update #sphelpcols set col_flags = col_flags | 0x0004 from #sphelpcols c, sysindexkeys i where i.id = @objid and i.indid = @indid and i.colid = c.col_id end /* OK, now put out the data. @flags2 added for DaVinci; currently only bit 1 (sp_help filtering of prec/scale) is relevant. */ set nocount off IF (@orderby is null or @orderby = N'id') BEGIN /**** Begin code added by rahul maheshwari on Oct 1, 2001 for generating the table design script ***/ DECLARE @tablestructure VARCHAR(1000), @min_col int, @maxlencol varchar(3), @maxlencol1 varchar(3) SELECT @tablestructure = 'CREATE TABLE ' + @tablename + char(13) + '(' SELECT c.col_name, c.col_id, c.col_typename, c.col_len, col_basetypename, col_prec , col_scale, c.col_null INTO #tmp_tble_struc from (#sphelpcols c left outer join syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid order by c.col_id SELECT @min_col = MIN(col_id) FROM #tmp_tble_struc SELECT @maxlencol = MAX(LEN(col_name)) + 1 FROM #tmp_tble_struc SELECT @maxlencol1 = MAX(LEN(col_typename)) + 6 FROM #tmp_tble_struc PRINT LTRIM(RTRIM(@tablestructure)) WHILE @min_col IS NOT NULL BEGIN SELECT @tablestructure = "PRINT Char(9)+" + "CONVERT(Char(" + @maxlencol + "),'" + col_name + "') + " +Char(9) + "CONVERT(Char(" + @maxlencol1 + "),'" + col_typename + CASE WHEN col_typename = 'Char' THEN '('+ CONVERT(VARCHAR(5),col_len) + ")'" WHEN col_typename = 'VarChar' THEN '('+ CONVERT(VARCHAR(5),col_len) + ")'" WHEN col_typename = 'Numeric' THEN '('+ CONVERT(VARCHAR(5),col_prec) +','+ CONVERT(VARCHAR(5),col_scale) + ")'" ELSE "'" END +')'+ char(9) + CASE WHEN col_null = 1 THEN " + CHAR(9)+ 'NULL'" ELSE "+CHAR(9)+ 'NOT NULL'" END FROM #tmp_tble_struc WHERE col_id = @min_col SELECT @min_col = MIN(col_id) FROM #tmp_tble_struc WHERE col_id > @min_col IF @min_col IS NOT NULL BEGIN SELECT @tablestructure = @tablestructure + "+','" END --Final Select EXEC (@tablestructure) END PRINT ')' /**** End code added by rahul maheshwari on Oct 1, 2001 for generating the table design script ***/ end else begin select c.col_name, c.col_id, c.col_typename, c.col_len, -- Prec/scale only for numeric/decimal col_prec = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_prec else NULL end, col_scale = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_scale else NULL end, col_basetypename, c.col_defname, c.col_rulname, c.col_null, c.col_identity, c.col_flags, c.col_seed, c.col_increment, c.col_dridefname, cn.text, c.col_iscomputed, cm.text, c.col_NotForRepl, c.col_fulltext, c.col_AnsiPad, c.col_DOwner, c.col_DName, c.col_ROwner, c.col_RName from (#sphelpcols c left outer join syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid order by c.col_name SET NOCOUNT OFF end

Answer Wiki

Thanks. We'll let you know when a new response is added.

Why not just use sp_help_table?

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following