Databases, Microsoft SQL Server

Retrieve useful information from SQL Server

Today we will see how can we retrieve various information from Microsoft SQL Server using SQL queries.

Get all users:

SELECT * FROM sys.server_principals;

Get database size:

USE master
GO
 
EXEC sp_spaceused

Get user privilleges for current database:

USE master
GO
 
SELECT SYSOBJECTS.name AS 'objectname', SYSUSERS.name AS 'username' ,
Action = CASE SYSPROTECTS.action
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'UPDATE'
WHEN 197 THEN 'DELETE'
WHEN 224 THEN 'EXEC'
ELSE 'UNKNOWN'
END,
STATUS =
CASE SYSPROTECTS.protecttype
WHEN 204 THEN 'GRANT_WITH_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'REVOKE'
ELSE 'UNKNOWN'
END
FROM master.dbo.SYSUSERS SYSUSERS, SYSOBJECTS SYSOBJECTS, SYSPROTECTS SYSPROTECTS
WHERE SYSUSERS.uid = SYSOBJECTS.uid AND
SYSOBJECTS.id = SYSPROTECTS.id;

Get available stored procedures for current database:

USE master
GO
 
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
FROM sys.objects 
WHERE type_desc LIKE '%SQL_STORED_PROCEDURE%';

Get foreign keys for current database:

USE master
GO
 
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
FROM sys.objects 
WHERE type_desc LIKE '%FOREIGN_KEY_CONSTRAINT%';