create procedure sys.sp_MShelpcolumns
@tablename nvarchar(517), @flags int = 0, @orderby nvarchar(10) = null, @flags2 int = 0
as
/* For non-string columns, sp_MShelpcolumns returns the length in syscolumns.length, */
/* which is defined in BOL as "maximum physical storage length from systypes". */
/* For string columns (including types based on string types), sp_MShelpcolumns */
/* returns this maximum length in characters (i.e. it returns syscolumns.length */
/* adjusted to whether the column is based on char or nchar). */
/*** @flags2 added for DaVinci uses. If the bit isn't set, use 6.5 ***/
/*** sp_MShelpcolumns '%s', null, 'id', 1 ***/
create table #sphelpcols
(
col_name nvarchar(128) COLLATE database_default NOT NULL,
col_id int NOT NULL,
col_typename nvarchar(128) COLLATE database_default 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) COLLATE database_default NULL, /* fully-qual'd default name, or NULL */
col_rulname nvarchar(257) COLLATE database_default NULL, /* fully-qual'd rule name, or NULL */
col_basetypename nvarchar(128) COLLATE database_default NOT NULL,
col_flags int NULL, /* COL_* bits */
/* Fix for Raid # 53682 */
col_seed nvarchar (40) COLLATE database_default NULL,
/* col_seed numeric (28) NULL, */
col_increment nvarchar (40) COLLATE database_default NULL,
/* col_increment int NULL, */
col_dridefname nvarchar(128) COLLATE database_default 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) COLLATE database_default NULL, /* non-DRI DEFAULT owner, or NULL */
col_DName nvarchar(128) COLLATE database_default NULL, /* non-DRI DEFAULT name, or NULL */
col_ROwner nvarchar(128) COLLATE database_default NULL, /* non-DRI RULE owner, or NULL */
col_RName nvarchar(128) COLLATE database_default NULL, /* non-DRI RULE name, or NULL */
col_collation nvarchar(128) COLLATE database_default NULL, /* column level collation, valid for string columns only */
col_isindexable int,
col_language int,
)
/** For DaVinci **/
/** Use sp_help filtering of precision/scale (only fordecimal/numeric types; else use NULL). **/
if @flags is null
select @flags = 0
if (@tablename = N'?')
begin
print N''
print N'Usage: sp_MShelpcolumns @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
/* 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',N'bigint'))
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 schema_name(sysod.schema_id) + N'.' + d.name end,
-- Non-DRI Rule
case when (c.domain = 0) then null else schema_name(sysor.schema_id) + 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
/* Fix for Raid # 53682 */
case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_seed(@tablename)) else null end,
/* 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 CONVERT(nvarchar(40), ident_incr(@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 schema_name(sysod.schema_id) 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 schema_name(sysor.schema_id) end,
case when (c.domain = 0) then null else r.name end,
-- column level collation
c.collation,
-- IsIndexable
ColumnProperty(@objid, c.name, N'IsIndexable'),
c.language
from dbo.syscolumns c
-- NonDRI Default and Rule filters
left outer join (dbo.sysobjects d join sys.all_objects sysod on d.id = sysod.object_id) on d.id = c.cdefault
left outer join (dbo.sysobjects r join sys.all_objects sysor on r.id = sysor.object_id) on r.id = c.domain
-- Fully derived data type name
join dbo.systypes st on st.xusertype = c.xusertype
-- Physical base data type name
join dbo.systypes bt on bt.xusertype = c.xtype
-- DRIDefault text, if it's only one row.
left outer join dbo.syscomments t on t.id = c.cdefault and t.colid = 1
and not exists (select * from dbo.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.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, dbo.systypes n, dbo.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 dbo.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, dbo.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
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,
collation = c.col_collation,
ColType = case when( col_basetypename in (N'image')) then d.FT_COLNAME else NULL end, /* FullText column name for image column */
case when ( c.col_isindexable is null ) then 0 else c.col_isindexable end,
case when ( c.col_language >= 0 ) then c.col_language else -1 end
from ((#sphelpcols c
left outer join dbo.syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join dbo.syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid)
left outer join (select distinct FT_COLNAME = scol.name, FT_ID = sdep.number from dbo.syscolumns scol, dbo.sysdepends sdep where
scol.colid = sdep.depnumber and
sdep.deptype = 1 and
scol.id = @objid and
sdep.depid = @objid and
ColumnProperty(scol.id, scol.name, N'IsTypeForFullTextBlob') = 1) as d on c.col_id = d.FT_ID
order by c.col_id
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,
collation = c.col_collation,
ColType = case when( col_basetypename in (N'image')) then d.FT_COLNAME else NULL end, /* FullText column name for image column */
case when ( c.col_isindexable is null ) then 0 else c.col_isindexable end,
case when ( c.col_language >= 0 ) then c.col_language else -1 end
from ((#sphelpcols c
left outer join dbo.syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join dbo.syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid)
left outer join (select distinct FT_COLNAME = scol.name, FT_ID = sdep.number from dbo.syscolumns scol, dbo.sysdepends sdep where
scol.colid = sdep.depnumber and
sdep.deptype = 1 and
scol.id = @objid and
sdep.depid = @objid and
ColumnProperty(sdep.id, scol.name, N'IsTypeForFullTextBlob') = 1) as d on c.col_id = d.FT_ID
order by c.col_name
end
