The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_MShelpcolumns(nvarchar @tablename, int @flags
, nvarchar @orderby
, int @flags2)
MetaData:
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.<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, 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
No comments:
Post a Comment