May 16, 2012

sp_MShelpcolumns (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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

Total Pageviews