Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MShelptype

  No additional text.


Syntax
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

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash