LetsGoRafting

create table #Backupsizes 
(dbname varchar(64), 
filedate datetime, 
Dsize real, 
Lsize real, 
Fullsize real,
Logcount int,
Logtotal real, 
LogAvg real)

LetsGoRafting

Use <DBName>;

Select OBJECT_SCHEMA_NAME(I.object_id) + N'.' + OBJECT_NAME(I.object_id) As TableName,
	I.name As IndexName,
	I.index_id As IndexID,
	IUS.user_seeks,
	IUS.user_scans,
	IUS.user_lookups,
	IUS.user_updates,
	IUS.last_user_seek,

LetsGoRafting

-- MOST USEFUL of all the scripts herein:


SET NOCOUNT ON
DECLARE @endDate datetime, @months smallint; 
SET @endDate = GetDate();  -- Data atual
SET @months = 12;          -- Nr. de meses a analisar

;WITH HIST AS 
   (SELECT BS.database_name AS DatabaseName 

LetsGoRafting

-- Last updated October 1, 2021
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]

LetsGoRafting

SELECT [Schema] = schema_name(o.schema_id), o.Name, o.type 
FROM sys.sql_modules m
INNER JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.definition like '%mytexttofind%'
GO

LetsGoRafting

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

select 'delete from ' + name
from sys.objects
where type = 'U'
and name like 'blah-di-blah%'
order by name

-- SSIS that shizzle in there and flip them on again

LetsGoRafting

/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases. 
Also, you can use this script to get the roles of one user in all the databases. 

Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
1. Find this code and u.name like ''tester''

LetsGoRafting

declare @user sysname
declare @domain varchar(100)
 
set @domain = 'mydomain'
 
declare recscan cursor for
select name from sys.server_principals
where type = 'U' and name like @domain+'%'
 
open recscan 

LetsGoRafting

DECLARE @file varchar(255);
SELECT @file = path FROM sys.traces WHERE is_default = 1;
WITH TraceCTE AS (
    SELECT DatabaseID, 
        DatabaseName, 
        LoginName, 
        HostName, 
        ApplicationName, 
        SPID,
        StartTime,

LetsGoRafting

use msdb
select [job]=j.name, [step]=s.step_name, s.command 
from dbo.sysjobsteps s
inner join dbo.sysjobs j
on s.job_id = j.job_id
and s.subsystem ='SSIS' 
go