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_special_columns(nvarchar @table_name, nvarchar @table_owner
, nvarchar @table_qualifier
, char @col_type
, char @scope
, char @nullable
, int @ODBCVer)
MetaData:
create procedure sys.sp_special_columns ( @table_name sysname, -- Wildcard pattern matching IS NOT supported. @table_owner sysname = null, -- Wildcard pattern matching IS NOT supported. @table_qualifier sysname = null, @col_type char(1) = 'R', @scope char(1) = 'T', -- Not used in the search criteria. @nullable char(1) = 'U', @ODBCVer int = 2 ) as declare @index_id int declare @table_id int -- quotename() returns up to 258 chars declare @full_table_name nvarchar(517) -- 258 + 1 + 258 declare @scopeout smallint if @col_type not in ('R','V') or @col_type is null begin raiserror (15251,-1,-1,'col_type','''R'' or ''V''') return end if @scope = 'C' select @scopeout = 0 else if @scope = 'T' select @scopeout = 1 else begin raiserror (15251,-1,-1,'scope','''C'' or ''T''') return end if @nullable not in ('U','O') or @nullable is null begin raiserror (15251,-1,-1,'nullable','''U'' or ''O''') return end if @table_qualifier is not null begin if db_name() <> @table_qualifier begin -- If qualifier doesn't match current database raiserror (15250, -1,-1) return end end if @table_owner is null begin -- If unqualified table name select @full_table_name = quotename(@table_name) end else begin -- Qualified table name if @table_owner = '' begin -- If empty owner name select @full_table_name = quotename(@table_owner) end else begin select @full_table_name = quotename(@table_owner) + '.' + quotename(@table_name) end end -- Get Object ID select @table_id = object_id(@full_table_name) if (@table_id is null) -- Return empty resultset if table doesn't exist begin select SCOPE = convert(smallint,0), COLUMN_NAME = convert(sysname,NULL), DATA_TYPE = convert(smallint,0), TYPE_NAME = convert(sysname,NULL), "PRECISION" = convert(int,0), "LENGTH" = convert(int,0), SCALE = convert(smallint,0), PSEUDO_COLUMN = convert(smallint,0) where 1=0 return end if @col_type = 'V' begin -- if ROWVER, just run that query select SCOPE = convert(smallint,NULL), COLUMN_NAME = convert(sysname,c.name), DATA_TYPE = convert(smallint, -2), TYPE_NAME = t.name, "PRECISION" = convert(int,8), "LENGTH" = convert(int,8), SCALE = convert(smallint, NULL), PSEUDO_COLUMN = convert(smallint,1) from sys.types t, sys.all_columns c where c.object_id = @table_id and t.name = 'timestamp' and t.user_type_id = c.system_type_id and t.user_type_id = c.user_type_id return end -- ROWID, now find the id of the 'best' index for this table if @nullable = 'O' -- Don't include any indexes that contain nullable columns. select @index_id = MIN(x.index_id) from sys.indexes x, sys.all_columns c, sys.all_columns c2 where x.is_unique = 1 and -- If Unique Index x.object_id = @table_id and x.index_id > 0 and -- Eliminate Table Row c.object_id = x.object_id and c2.object_id = c.object_id and c.name = index_col(@table_name,x.index_id,c2.column_id) group by x.index_id HAVING SUM(convert (int, c.is_nullable)) = 0 else -- Include indexes that are partially nullable. select @index_id = MIN(x.index_id) from sys.indexes x where x.is_unique = 1 and -- If Unique Index x.object_id = @table_id and x.index_id > 0 -- Eliminate Table Row select SCOPE = @scopeout, COLUMN_NAME = convert(sysname,c.name), DATA_TYPE = convert(smallint, case when (d.ss_dtype = 240) then -- CLR UDT -4 when (d.ss_dtype = 241) then -- XML -10 when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max) -1 when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max) -10 when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max) -4 when d.ss_dtype IN (40,41,42,43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET -9 -- SQL_WVARCHAR else d.DATA_TYPE end), TYPE_NAME = convert(sysname,case when (t.system_type_id = 240 or t.user_type_id > 255) then t.name else d.TYPE_NAME collate catalog_default end), "PRECISION" = convert(int,case when d.DATA_TYPE in (6,7) then d.data_precision -- FLOAT/REAL when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients 2147483647 else OdbcPrec(c.system_type_id,c.max_length,c.precision) end), "LENGTH" = convert(int,case when type_name(d.ss_dtype) IN ('numeric','decimal') then -- decimal/numeric types OdbcPrec(c.system_type_id,c.max_length,c.precision)+2 when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients 2147483647 when d.ss_dtype IN (40,41,42,43) then OdbcPrec(c.system_type_id,c.max_length,c.precision)*2 -- DATE/TIME/DATETIME2/DATETIMEOFFSET else isnull(d.length, c.max_length) end), SCALE = convert(smallint,case when d.ss_dtype IN (40,41,42,43) then null -- DATE/TIME/DATETIME2/DATETIMEOFFSET else OdbcScale(c.system_type_id,c.scale) end), PSEUDO_COLUMN = convert(smallint,1) from sys.columns c inner join sys.indexes x on ( x.object_id = c.object_id and x.object_id = @table_id and x.index_id = @index_id ) inner join sys.types t on ( t.user_type_id = c.user_type_id ) inner join sys.spt_datatype_info d on ( d.ss_dtype = c.system_type_id and d.ODBCVer = @ODBCVer and d.AUTO_INCREMENT = c.is_identity ) inner join sys.columns c2 on -- Self-join to generate list of index columns and to extract datatype names. ( INDEX_COL(@full_table_name,@index_id,c2.column_id) = c.name and c2.object_id = x.object_id )
No comments:
Post a Comment