iVend Database Database : uspGetSQLSchemaInfo Stored Procedure

Creation Date

9/18/2015 10:23 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@SQLSatement

In

VarWChar

1073741823

@RETURN_VALUE

Return Value

Integer

4

-- [uspGetSQLSchemaInfo] 'Select * from customer,address'

CREATE PROC [dbo].[uspGetSQLSchemaInfo]

@SQLSatement nvarchar(max)

AS

Begin

if(@SQLSatement='') return

Declare @ColumnSQLStatement nvarchar(max)

SET @ColumnSQLStatement='Declare cursorGetSchemaInfo Cursor For

' + @SQLSatement + '

FOR READ ONLY

SELECT Object_Name(objectid) as table_Name, column_name

    FROM sys.syscursorrefs scr, sys.syscursorcolumns scc

    WHERE  scr.cursor_scope =2 and

     scr.reference_name = ''cursorGetSchemaInfo'' and

     scr.cursor_handl = scc.cursor_handle

     AND scc.data_type_sql NOT IN (34, 165, 173, 189, 241) --Image, varbinary, binary, timestamp, xml

    ORDER BY 1

Deallocate  cursorGetSchemaInfo'

Exec Sp_executesql @ColumnSQLStatement

End