April 17, 2012

sp_columns_managed (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_columns_managed(nvarchar @Catalog
, nvarchar @Owner
, nvarchar @Table
, nvarchar @Column
, nvarchar @SchemaType)

MetaData:

   
create procedure sys.sp_columns_managed
(
@Catalog sysname = NULL,
@Owner sysname = NULL,
@Table sysname = NULL,
@Column sysname = NULL,
@SchemaType sysname = 0 ) -- 0 = 'select *' behavior (default), 1 = all columns, 2 = columnset columns
as
select
TABLE_CATALOG = s_cv.TABLE_CATALOG,
TABLE_SCHEMA = s_cv.TABLE_SCHEMA,
TABLE_NAME = s_cv.TABLE_NAME,
COLUMN_NAME = s_cv.COLUMN_NAME,
ORDINAL_POSITION = s_cv.ORDINAL_POSITION,
COLUMN_DEFAULT = s_cv.COLUMN_DEFAULT,
IS_NULLABLE = s_cv.IS_NULLABLE,
DATA_TYPE = s_cv.DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH= s_cv.CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH = s_cv.CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION = s_cv.NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX = s_cv.NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE = s_cv.NUMERIC_SCALE,
DATETIME_PRECISION = s_cv.DATETIME_PRECISION,
CHARACTER_SET_CATALOG = s_cv.CHARACTER_SET_CATALOG,
CHARACTER_SET_SCHEMA = s_cv.CHARACTER_SET_SCHEMA,
CHARACTER_SET_NAME = s_cv.CHARACTER_SET_NAME,
COLLATION_CATALOG = s_cv.COLLATION_CATALOG,
IS_SPARSE = s_cv.IS_SPARSE,
IS_COLUMN_SET = s_cv.IS_COLUMN_SET,
IS_FILESTREAM = s_cv.IS_FILESTREAM
from
sys.spt_columns_view_managed s_cv
where
(@Catalog is null or s_cv.TABLE_CATALOG like @Catalog) and
(@Owner is null or s_cv.TABLE_SCHEMA like @Owner) and
(@Table is null or s_cv.TABLE_NAME like @Table) and
(@Column is null or s_cv.COLUMN_NAME like @Column) and
(
@SchemaType = 0 AND
(
( s_cv.IS_SPARSE = 0 OR objectproperty ( s_cv.OBJECT_ID, 'tablehascolumnset' ) = 0 )
)
OR @SchemaType = 1
OR @SchemaType = 2 AND
(
( s_cv.IS_SPARSE = 1 AND objectproperty ( s_cv.OBJECT_ID, 'tablehascolumnset' ) = 1 )
)
)
order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, IS_NULLABLE

No comments:

Post a Comment

Total Pageviews