USP_SCHEMAINFO_GETVIEWCOLUMNINFO
Returns column information for a specific view, table, or table valued function.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @VIEWNAME | nvarchar(128) | IN | |
| @VIEW_SCHEMA | nvarchar(128) | IN |
Definition
Copy
CREATE procedure dbo.USP_SCHEMAINFO_GETVIEWCOLUMNINFO
(
@VIEWNAME nvarchar(128),
@VIEW_SCHEMA nvarchar(128)='dbo'
)
as
if exists(select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@VIEWNAME and TABLE_SCHEMA=@VIEW_SCHEMA)
begin
select
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
DOMAIN_NAME,
(select
sys.columns.IS_COMPUTED
from
sys.columns
inner join sys.objects on sys.objects.OBJECT_ID = sys.columns.OBJECT_ID
inner join sys.schemas on sys.objects.SCHEMA_ID = sys.schemas.SCHEMA_ID
where
sys.objects.NAME = TABLE_NAME and sys.columns.NAME = COLUMN_NAME and sys.schemas.NAME = @VIEW_SCHEMA
) as ISCOMPUTED,
NUMERIC_PRECISION,
NUMERIC_SCALE
from
INFORMATION_SCHEMA.COLUMNS
where
TABLE_SCHEMA=@VIEW_SCHEMA and
TABLE_NAME=@VIEWNAME
order by
ORDINAL_POSITION
end
else
begin
select
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
DOMAIN_NAME,
(select
sys.columns.IS_COMPUTED
from
sys.columns
inner join sys.objects on sys.objects.OBJECT_ID = sys.columns.OBJECT_ID
inner join sys.schemas on sys.objects.SCHEMA_ID = sys.schemas.SCHEMA_ID
where
sys.objects.NAME = TABLE_NAME and sys.columns.NAME = COLUMN_NAME and sys.schemas.NAME = @VIEW_SCHEMA
) as ISCOMPUTED,
NUMERIC_PRECISION,
NUMERIC_SCALE
from
INFORMATION_SCHEMA.ROUTINE_COLUMNS
where
TABLE_SCHEMA=@VIEW_SCHEMA and
TABLE_NAME=@VIEWNAME
order by
ORDINAL_POSITION
end