Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

create procedure sys.sp_MSget_type
    @tabid int,
    @colid int,
    @colname sysname output,
    @typestring nvarchar(4000) output,
    @userdefinedtypestobasetypes bit = 0,
    @xmltontext bit = 0,
    @maxtypestomatchingnonmaxtypes bit = 0,
    @newdatetimetostrings bit = 0
    declare @ccolchar nvarchar(5)
                ,@coltype tinyint
                ,@prec int
                ,@scale int
                ,@ccoltype sysname
                ,@xtype int
                ,@length int
                ,@is_assembly_type bit
                ,@type_schema_id int

    declare @retcode int

    -- get the metadata (handle UDF properly)
    select @colname = c.name ,@xtype = c.system_type_id, @length = c.max_length, @prec = c.precision, @scale = c.scale
        ,@ccoltype = t.name, @is_assembly_type = t.is_assembly_type, @type_schema_id = t.schema_id
    from (sys.columns c join sys.types t
                on ((c.system_type_id != 240 and c.system_type_id = t.user_type_id) or (c.system_type_id = 240 and c.user_type_id = t.user_type_id)) )
    where c.object_id = @tabid and c.column_id = @colid
    -- initialize the result string
    select @typestring = @ccoltype
    -- datatypes requiring precision (nchar, nvarchar, binary, varbinary)
    -- format: @var 
(prec) if @ccoltype in (N'char',N'nchar', N'varchar', N'nvarchar', N'binary', N'varbinary') begin if (@length = -1) begin -- precision not required for varchar(max), varbinary(max), nvarchar(max) if @maxtypestomatchingnonmaxtypes = 1 select @typestring = NULL else select @typestring = @typestring + N'(max)' end else begin -- precision required if (@ccoltype in (N'nchar', N'nvarchar')) select @length = @length/2 select @typestring = @typestring + N'(' + rtrim(convert(nchar(10),@length)) + N')' end end else if @ccoltype in (N'numeric', N'decimal') begin -- datatypes requiring precision & scale (numeric & decimal) -- format: @var
(prec, scale) select @typestring = @typestring + N'(' + rtrim(convert(nchar(10),ColumnProperty(@tabid, @colname, 'PRECISION'))) + N',' + rtrim(convert(nchar(10),@scale)) + N')' end else if @ccoltype in (N'text',N'ntext',N'image') begin -- text/image datatypes cannot be declared or used locally select @typestring = NULL end else if @ccoltype in (N'date') begin if @newdatetimetostrings = 1 begin set @typestring = N'nvarchar(10)' end end else if @ccoltype in (N'datetime2', N'datetimeoffset', N'time') begin if @newdatetimetostrings = 1 begin select @typestring = case @ccoltype when N'datetime2' then N'nvarchar(27)' when N'datetimeoffset' then N'nvarchar(34)' when N'time' then N'nvarchar(16)' end end else begin set @typestring = @typestring + N'(' + rtrim(convert(nvarchar(10), @scale)) + ')' end end else if @is_assembly_type = 1 begin if (@userdefinedtypestobasetypes = 1) begin -- CLR UDT is converted to image, set @typestring to NULL select @typestring = NULL end else begin -- Prefix the schema name to CLR UDT type select @typestring = quotename(schema_name(@type_schema_id)) + N'.' + quotename(@ccoltype) end end else if @ccoltype in (N'xml') and @xmltontext = 1 begin -- XML type is converted to ntext, set @typestring to NULL select @typestring = NULL end -- all done return 0 end
Last revision 2008RTM
See also

  sp_getqueuedrows (Procedure)
sp_MSadd_merge_partition_column (Procedure)
sp_MSget_type_wrapper (Procedure)
sp_MSmakeconflicttable (Procedure)
sp_MSmaketrancftproc (Procedure)
sp_MSscript_params (Procedure)
sp_MSscript_trigger_variables (Procedure)
sp_MStable_not_modifiable (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash