April 18, 2012

sp_describe_cursor (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_describe_cursor(nvarchar @cursor_source
, nvarchar @cursor_identity)

MetaData:

 --  Creation of sp_describe_cursor  

create procedure sys.sp_describe_cursor
( @cursor_return CURSOR VARYING OUTPUT,
@cursor_source nvarchar (30),
@cursor_identity nvarchar (128)
)
AS

declare @scope int
select @cursor_source = LOWER (@cursor_source collate Latin1_General_CI_AS)

-- Check if the cursor exists by name or handle. --
If cursor_status ( @cursor_source, @cursor_identity ) >= -1
begin
if convert(varchar(30), @cursor_source) = 'local' OR
convert(varchar(128), @cursor_source) = 'variable'
select @scope = 1
else
if convert(varchar(30), @cursor_source) = 'global'
select @scope = 2


set @cursor_return = CURSOR LOCAL SCROLL DYNAMIC FOR
SELECT reference_name, cursor_name, cursor_scope,
status, model, concurrency, scrollable,
open_status, cursor_rows, fetch_status,
column_count, row_count, last_operation,
cursor_handle
FROM sys.syscursorrefs scr, sys.syscursors sc
WHERE scr.cursor_scope = @scope and
scr.reference_name = @cursor_identity and
scr.cursor_handl = sc.cursor_handle
ORDER BY 3, 1
FOR READ ONLY
open @cursor_return

end

No comments:

Post a Comment

Total Pageviews