April 30, 2012

sp_helpsrvrole (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_helpsrvrole(nvarchar @srvrolename)

MetaData:

 create procedure sys.sp_helpsrvrole  
@srvrolename sysname = NULL
AS
if @srvrolename is not null
begin
-- VALIDATE GIVEN NAME
if not exists (select * from master.dbo.spt_values
where name = @srvrolename and low = 0 and type = 'SRV')
begin
raiserror(15412, -1, -1, @srvrolename)
return (1)
end

-- RESULT SET FOR SINGLE SERVER-ROLE
-- UNDONE: REMOVE THE "Description" OUTPUT (should be BOL only)
select 'ServerRole' = v1.name, 'Description' = v2.name
from master.dbo.spt_values v1, master.dbo.spt_values v2
where v1.name = @srvrolename and
v1.low = 0 and
v1.type = 'SRV' and
v2.low = -1 and
v2.type = 'SRV' and
v1.number = v2.number
end
else
begin
-- RESULT SET FOR ALL SERVER-ROLES
-- UNDONE: REMOVE THE "Description" OUTPUT (should be BOL only)
select 'ServerRole' = v1.name, 'Description' = v2.name
from master.dbo.spt_values v1, master.dbo.spt_values v2
where v1.low = 0 and
v1.type = 'SRV' and
v2.low = -1 and
v2.type = 'SRV' and
v1.number = v2.number
end

return (0) -- sp_helpsrvrole

No comments:

Post a Comment

Total Pageviews