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