SQL Server Snippets

- SQL Server Snippets

.

drop-all-tables-in-schema.sql

DECLARE @SchemaName varchar(100) = 'Data_Marketplace';
DECLARE @DB_ID INT = DB_ID();

DECLARE @drop varchar(8000) = '';  
SELECT @drop = @drop + 'DROP TABLE [' + a.TABLE_SCHEMA + '].[' + a.TABLE_NAME + '];'  
FROM INFORMATION_SCHEMA.TABLES a
WHERE DB_ID(a.TABLE_CATALOG) = @DB_ID
      AND a.TABLE_SCHEMA = @SchemaName;
EXEC(@drop);

drop-all-triggers-in-schema.sql

DECLARE @DB_ID INT = DB_ID();
DECLARE @SCHEMA_NAME VARCHAR(128) = '<Schema-Name-Here>';
DECLARE @drop varchar(8000) = '';  
SELECT @drop = @drop + 'DROP TRIGGER [' + @SCHEMA_NAME + '].[' + o.NAME + '];'  
  FROM SYS.OBJECTS o
WHERE o.schema_id=SCHEMA_ID(@SCHEMA_NAME)
      AND o.TYPE IN ('TR')
EXEC(@drop);

kill-all-sessions.sql

-- USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = DB_ID() AND session_id <> @@SPID

EXEC(@kill);

list-all-sessions.sql

SELECT * 
FROM sys.dm_exec_sessions
WHERE database_id  = DB_ID();

list-blocking-locks.sql

SELECT p.cmd,
       p.*
  FROM sys.sysprocesses p
 WHERE blocked > 0

list-principal-permissions.sql

select sys.schemas.name 'Schema',
       sys.objects.name Object,
	   sys.database_principals.name username,
       sys.database_permissions.type permissions_type,
	   sys.database_permissions.permission_name,
	   sys.database_permissions.state permission_state,
	   sys.database_permissions.state_desc,
	   state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
     left outer join sys.objects on sys.database_permissions.major_id = sys.objects.object_id
     left outer join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
     left outer join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
WHERE sys.database_principals.name = 'principal-name'
order by 1, 2, 3, 5;

list-principal-roles.sql

DECLARE @PrincipalName VARCHAR(128) = 'principal-name-here'
SELECT r.name role_principal_name, 
       m.name AS member_principal_name
  FROM sys.database_role_members rm 
  JOIN sys.database_principals r 
       ON rm.role_principal_id = r.principal_id
  JOIN sys.database_principals m 
       ON rm.member_principal_id = m.principal_id
 WHERE r.type = 'R'
       AND m.name = @PrincipalName;

list-tables-in-schema.sql

DECLARE @SchemaName varchar(100) = '<Schema-goes-here>';

SELECT a.TABLE_CATALOG as 'Database',
       a.TABLE_SCHEMA as 'Schema',
	   a.TABLE_NAME as 'Table Name',
	   a.TABLE_TYPE as 'Table Type' 
FROM INFORMATION_SCHEMA.TABLES a
WHERE DB_ID(a.TABLE_CATALOG) = DB_ID()
      AND a.TABLE_SCHEMA = @SchemaName;

list-tables-in-schema1.sql

DECLARE @SchemaName VARCHAR(128) = '<Schema-Name-Here>'
SELECT SCHEMA_NAME(a.schema_id) as 'Schema ID', 
       a.name as 'Name', 
       a.type as 'Type', 
       a.type_desc as 'Type Description', 
       a.create_date as 'Create Date'
FROM SYS.OBJECTS a 
WHERE TYPE IN ('U')
      AND a.schema_id = SCHEMA_ID(@SchemaName);

list-users.sql

select name as username,
       create_date,
       modify_date,
       type_desc as type,
	   type as t,
       authentication_type_desc as authentication_type
from sys.database_principals
where 1 = 1
      and type not in ('A', 'G', 'R')
      and sid is not null
      and name != 'guest'
order by username;