May 16, 2012

sp_MShasdbaccess (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_MShasdbaccess()

MetaData:

 --  =============================================  
-- sp_MShasdbaccess
-- =============================================
-- List all databases a user has access to
-- along with their db properties
--
-- PARAMETERS: N/A
--
-- REMARKS: for SQL Server 7.0 and 8.0
-- =============================================
create proc sys.sp_MShasdbaccess
as

set nocount on
set deadlock_priority low

select name as 'dbname',
owner = substring(suser_sname(sid), 1, 24),
(CASE WHEN DATABASEPROPERTYEX(name, 'UserAccess') = 'RESTRICTED_USER' THEN 1 ELSE 0 END) as 'DboOnly',
(SELECT is_read_only FROM sys.databases WHERE name=sys.sysdatabases.name) as 'ReadOnly',
(CASE WHEN DATABASEPROPERTYEX(name, 'UserAccess') = 'SINGLE_USER' THEN 1 ELSE 0 END) as 'SingleUser',
NULL as 'Detached', -- DATABASEPROPERTY(name, N'IsDetached') always return NULL since IsDetached property doesn't exist
(CASE WHEN DATABASEPROPERTYEX(name, 'Status') = 'SUSPECT' THEN 1 ELSE 0 END) as 'Suspect',
(CASE WHEN DATABASEPROPERTYEX(name, 'Status') = 'OFFLINE' THEN 1 ELSE 0 END) as 'Offline',
(CASE WHEN DATABASEPROPERTYEX(name, 'Status') = 'RESTORING' THEN 1 ELSE 0 END) as 'InLoad',
(CASE WHEN DATABASEPROPERTYEX(name, 'Status') = 'EMERGENCY' THEN 1 ELSE 0 END) as 'EmergencyMode',
CONVERT(INT, DATABASEPROPERTYEX(name, N'IsInStandBy')) as 'StandBy',
(CASE WHEN DATABASEPROPERTYEX(name, 'Status') = 'SUSPECT' THEN 1 ELSE 0 END) as 'ShutDown', -- Both 'IsShutDown' and 'Suspect' are now mapped to 'Suspect'.
(CASE WHEN DATABASEPROPERTYEX(name, 'Status') = 'RECOVERING' THEN 1 ELSE 0 END) as 'InRecovery',
NULL as 'NotRecovered' -- DATABASEPROPERTY(name, N'IsNotRecovered') always return NULL since IsNotRecovered property doesn't exist

from sys.sysdatabases
where has_dbaccess(name) = 1
order by name
-- =============================================
-- end sp_MShasdbaccess
-- =============================================

No comments:

Post a Comment

Total Pageviews