USP_QUERYVIEW_GETCOLUMNLENGTHS

Returns the column name and length of all nvarchar, varchar and varbinary fields used in the specified query view.

Parameters

Parameter	Parameter Type	Mode	Description
@OBJECTNAME	nvarchar(1024)	IN
@OBJECTTYPE	int	IN

Definition

Copy



create procedure USP_QUERYVIEW_GETCOLUMNLENGTHS(@OBJECTNAME nvarchar(1024), @OBJECTTYPE integer)
as
if @OBJECTTYPE = 0 
    SELECT COLUMN_NAME, 
        DATA_TYPE, 
        CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE (TABLE_SCHEMA = 'dbo') and (TABLE_NAME = @OBJECTNAME) and (DATA_TYPE = 'nvarchar' or DATA_TYPE = 'varchar' or DATA_TYPE = 'varbinary')
else
    select 
    COLUMN_NAME, 
        DATA_TYPE, 
        CHARACTER_MAXIMUM_LENGTH
    from INFORMATION_SCHEMA.ROUTINE_COLUMNS where TABLE_NAME = @OBJECTNAME  and (DATA_TYPE = 'nvarchar' or DATA_TYPE = 'varchar' or DATA_TYPE = 'varbinary')