Skip to content

Instantly share code, notes, and snippets.

@ivangeorgiev
Last active April 17, 2024 10:33
Show Gist options
  • Save ivangeorgiev/8db4631372cc8a9e8981f51860524143 to your computer and use it in GitHub Desktop.
Save ivangeorgiev/8db4631372cc8a9e8981f51860524143 to your computer and use it in GitHub Desktop.
SQL Server Snippets
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);
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);
-- 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);
SELECT *
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID();
SELECT p.cmd,
p.*
FROM sys.sysprocesses p
WHERE blocked > 0
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;
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;
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;
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);
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment