June 8, 2012

sp_special_columns (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_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

Total Pageviews