May 21, 2012

sp_MShelptype (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_MShelptype(nvarchar @typename
, nvarchar @flags)

MetaData:

 create procedure sys.sp_MShelptype  
@typename nvarchar(517) = null, @flags nvarchar(10) = null
as

-- Need a temp table so we can ownerqualify nonNULL rules/defaults. --
create table #sphelptype (
dt_xusertype int NULL,
dt_basetype nvarchar(128) COLLATE database_default NULL,
dt_rul int NULL,
dt_def int NULL,

dt_rulowner nvarchar(128) COLLATE database_default NULL,
dt_rulname nvarchar(128) COLLATE database_default NULL,
dt_defowner nvarchar(128) COLLATE database_default NULL,
dt_defname nvarchar(128) COLLATE database_default NULL,
dt_flags int NULL
)

if (@typename = N'?')
begin
print N''
print N'Usage: sp_MShelptype @typename = null, @flags nvarchar(10) = null'
print N' where @flags is either:'
print N' sdt = look in system datatypes'
print N' uddt = look in user defined datatypes'
print N' null = look wherever its found'
print N''
return 0
end

-- Catch typos... --
if (@flags is not null and @flags not in (N'sdt', N'uddt'))
select @flags = null

-- Find out what type we're gonna be looking in, if they gave us a name. --
if (@typename is not null)
begin
declare @xusertype int
select @xusertype = xusertype from dbo.systypes where name = @typename
if (@xusertype is not null)
begin
if (@xusertype < 257)
begin
if (@flags is null)
select @flags = N'sdt'
if (@flags != N'sdt')
select @xusertype = null
end else begin
if (@flags is null)
select @flags = N'uddt'
if (@flags != N'uddt')
select @xusertype = null
end
end
if (@xusertype is null)
begin
RAISERROR (15001, -1, -1, @typename)
return 1
end
end

-- Now go get the info, depending on the type they gave us. --
if (@flags is null or @flags = N'sdt')
begin
-- Exclude the 'xxxxn' dblib-specific nullable types, and hardcode a check for variable length and numeric usertypes. --
-- 7.0 ifvarlen_max returns length for all the datatypes --
select SystemDatatypeName = t.name,
ifvarlen_max = y.length,
-- timestamp allows nulls even though the system tables say it doesn't.
allownulls = case when t.name in (N'timestamp') then 1 else t.allownulls end,
isnumeric = case when t.name in (N'decimal', N'numeric') then 1 else 0 end,
allowidentity = case when t.name in (N'decimal', N'int', N'numeric', N'smallint', N'tinyint', N'bigint') then 1 else 0 end,
variablelength = t.variable,
max_len = t.length, prec_len = t.prec,
collation = t.collation
from dbo.systypes t left outer join dbo.systypes y on t.xusertype = y.xusertype
and y.name in ( N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar' )
where t.xusertype < 257 and t.name not in (N'datetimn', N'decimaln', N'floatn', N'intn', N'moneyn', N'numericn') and (@typename is null or t.name = @typename)
order by t.name
end

if (@flags is null or @flags = N'uddt')
begin
set nocount on
insert #sphelptype (dt_xusertype, dt_basetype, dt_rul, dt_def, dt_flags)
select t.xusertype,
(select distinct b.name from dbo.systypes b where b.xtype = t.xtype and b.xusertype < 257 and b.name not in (N'sysname', N'timestamp', N'date', N'time')),
t.domain, t.tdefault, 0
from dbo.systypes t
where t.xusertype > 256 and (@typename is null or t.name = @typename)

-- Make a nice, presentable qualified rule/default name for those which are non-null --
update #sphelptype set dt_defowner = schema_name(d.schema_id)
from #sphelptype c, sys.all_objects d where c.dt_def is not null and d.object_id = c.dt_def
update #sphelptype set dt_defname = d.name
from #sphelptype c, sys.all_objects d where c.dt_def is not null and d.object_id = c.dt_def

update #sphelptype set dt_rulowner = schema_name(r.schema_id)
from #sphelptype c, sys.all_objects r where c.dt_rul is not null and r.object_id = c.dt_rul
update #sphelptype set dt_rulname = r.name
from #sphelptype c, sys.all_objects r where c.dt_rul is not null and r.object_id = c.dt_rul

-- For scripting, set the dt_flags -- these apply to the BASE datatype. --
update #sphelptype set dt_flags = dt_flags | 0x0001 where dt_basetype in ( N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar')
update #sphelptype set dt_flags = dt_flags | 0x0002 where dt_basetype in (N'numeric', N'decimal')

set nocount off
select distinct UserDatatypeName = t.name,
owner = schema_name(syst.schema_id),
-- The subquery fails if the current db is of a different collation from tempdb.
-- Also, not user why the subquery is being used in the 1st place
-- basetypename = (select distinct b.name from dbo.systypes b where b.name = s.dt_basetype),
basetypename = dt_basetype,
defaultname = dt_defname,
rulename = dt_rulname,
tid = t.xusertype,
length = case when s.dt_basetype in (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') then t.length else 0 end,
nullable = t.allownulls,
dt_prec = case when s.dt_basetype in (N'numeric', N'decimal') then t.prec else null end,
dt_scale = case when s.dt_basetype in (N'numeric', N'decimal') then t.scale else null end,
dt_flags,
allowidentity = case when (s.dt_basetype in (N'decimal', N'int', N'numeric', N'smallint', N'tinyint', N'bigint') and t.scale = 0) then 1 else 0 end,
variablelength = t.variable,
-- char count for string datatype, byte count for others --
maxlen = case when s.dt_basetype in (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') then t.prec else t.length end,
defaultowner = dt_defowner,
ruleowner = dt_rulowner,
collation = t.collation
from dbo.systypes t, sys.types syst, #sphelptype s
where t.xusertype > 256 and (@typename is null or t.name = @typename)
and dt_xusertype = t.xusertype
and t.xusertype = syst.user_type_id
order by t.name
end

No comments:

Post a Comment

Total Pageviews