use msdb;
go
declare @weekDay table
(
mask int,
maskValue varchar(32)
);
insert into @weekDay
(
mask,
maskValue
)
select 1, 'Sunday'
union all select 2, 'Monday'
union all select 4, 'Tuesday'
union all select 8, 'Wednesday'
union all select 16, 'Thursday'
union all select 32, 'Friday'
union all select 64, 'Saturday';
with myCTE
as
(
select
sched.name as 'scheduleName',
sched.enabled as SchedEnabled,
sched.schedule_id,
jobsched.job_id,
case
when sched.freq_type = 1 then 'Once'
when sched.freq_type = 4 and sched.freq_interval = 1 then 'Daily'
when sched.freq_type = 4 then 'Every ' + cast(sched.freq_interval as varchar(5)) + ' days'
when sched.freq_type = 8 then
replace(replace(replace(
(
select
maskValue
from
@weekDay x
where
sched.freq_interval & x.mask <> 0
order by
mask
for xml raw
),
'"/><row maskValue="',
', '
),
'<row maskValue="',
''
),
'"/>',
''
) + case
when sched.freq_recurrence_factor <> 0 and sched.freq_recurrence_factor = 1 then
'; weekly'
when sched.freq_recurrence_factor <> 0 then
'; every ' + cast(sched.freq_recurrence_factor as varchar(10)) + ' weeks'
end
when sched.freq_type = 16 then
'On day ' + cast(sched.freq_interval as varchar(10)) + ' of every '
+ cast(sched.freq_recurrence_factor as varchar(10)) + ' months'
when sched.freq_type = 32 then
case
when sched.freq_relative_interval = 1 then 'First'
when sched.freq_relative_interval = 2 then 'Second'
when sched.freq_relative_interval = 4 then 'Third'
when sched.freq_relative_interval = 8 then 'Fourth'
when sched.freq_relative_interval = 16 then 'Last'
end + case
when sched.freq_interval = 1 then ' Sunday'
when sched.freq_interval = 2 then ' Monday'
when sched.freq_interval = 3 then ' Tuesday'
when sched.freq_interval = 4 then ' Wednesday'
when sched.freq_interval = 5 then ' Thursday'
when sched.freq_interval = 6 then ' Friday'
when sched.freq_interval = 7 then ' Saturday'
when sched.freq_interval = 8 then ' Day'
when sched.freq_interval = 9 then ' Weekday'
when sched.freq_interval = 10 then ' Weekend'
end + case
when sched.freq_recurrence_factor <> 0 and sched.freq_recurrence_factor = 1 then '; monthly'
when sched.freq_recurrence_factor <> 0 then '; every ' + cast(sched.freq_recurrence_factor as varchar(10)) + ' months'
end
when sched.freq_type = 64 then 'StartUp'
when sched.freq_type = 128 then 'Idle'
end as Frequency,
isnull( 'Every ' + cast(sched.freq_subday_interval as varchar(10)) + case
when sched.freq_subday_type = 2 then ' seconds'
when sched.freq_subday_type = 4 then ' minutes'
when sched.freq_subday_type = 8 then ' hours'
end,
'Once'
) as SubFrequency,
replicate('0', 6 - len(sched.active_start_time)) + cast(sched.active_start_time as varchar(6)) as start_time,
replicate('0', 6 - len(sched.active_end_time)) + cast(sched.active_end_time as varchar(6)) as end_time,
replicate('0', 6 - len(jobsched.next_run_time)) + cast(jobsched.next_run_time as varchar(6)) as next_run_time,
cast(jobsched.next_run_date as char(8)) as next_run_date
from
msdb.dbo.sysschedules sched
inner join msdb.dbo.sysjobschedules jobsched on sched.schedule_id = jobsched.schedule_id
where
sched.enabled = 1
)
select
j.name as JobName,
j.enabled,
--j.category_id,
sp.name as JobOwner,
c.name as CategoryName,
--c.category_class,
js.step_id,
js.step_name,
js.subsystem,
js.command,
js.database_name,
js.database_user_name,
ct.next_run_date,
ct.next_run_time,
ct.start_time,
ct.end_time,
ct.Frequency,
ct.SubFrequency,
ct.scheduleName as ScheduleName,
ct.SchedEnabled --, ss.
from
dbo.sysjobs j
inner join dbo.sysjobsteps js on j.job_id = js.job_id
inner join dbo.syscategories c on j.category_id = c.category_id
inner join sys.server_principals sp on j.owner_sid = sp.sid
inner join myCTE ct on ct.job_id = j.job_id
where
(
j.name not like 'Log Shipping%' and
j.name not like 'z_%' and
j.name not like '%Full Backup%' and
j.name not like '%Create DBImage%' and
j.name not like '%System Database Maintenance T-SQL%'
)
order by JobName asc, js.step_id ascThis does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.
USE AdventureWorks2012;
GO
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';
GO Run this on the distributor server.
WITH cte AS
(
SELECT
msp.publication AS PublicationName,
msa.publisher_db AS DatabaseName,
msa.article AS ArticleName,
msa.source_owner AS SchemaName,
msa.source_object AS TableName
FROM distribution.dbo.MSarticles AS msa
JOIN distribution.dbo.MSpublications AS msp ON msa.publication_id = msp.publication_id
)
SELECT *
FROM cte
WHERE cte.DatabaseName = 'Postal' AND cte.ArticleName like '%Prepaid%'
ORDER BY
cte.PublicationName,
cte.DatabaseName,
cte.ArticleName-- How to use?
-- Set @TableName, OR
-- Comment out where clause to get table and index sizes for current database.
declare @TableName varchar(400) = 'database.schema.tablename'
-- No changes after this point
select
object_name(i.object_id) as TableName,
i.name as IndexName,
i.index_id as IndexID,
8 * sum(a.used_pages) as 'IndexSize(KB)',
convert(varchar, cast( (8 * sum(a.used_pages) ) / 1024 as money), 1) as 'IndexSize(MB)',
convert(varchar, cast( (8 * sum(a.used_pages) ) / 1024 / 1024 as money), 1) as 'IndexSize(GB)'
from
sys.indexes i
join sys.partitions p on p.object_id = i.object_id and p.index_id = i.index_id
join sys.allocation_units a on a.container_id = p.partition_id
where
i.object_id = object_id(@TableName)
group by
i.object_id,
i.index_id,
i.name
order by
object_name(i.object_id),
i.index_idselect top 10
databases.name,
dm_exec_sql_text.text as TSQL_Text,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time as total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
from
sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
cross apply sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
inner join sys.databases on dm_exec_sql_text.dbid = databases.database_id
where
dm_exec_sql_text.text like '%storedProcedureName%';Update statistics tablenameexec sp_updatestatsselect permission_name, state_desc, type_desc, U.name, OBJECT_NAME(object_id('dbo.SomeTagble'))
from sys.database_permissions P
JOIN sys.tables T ON P.major_id = T.object_id
JOIN sysusers U ON U.uid = P.grantee_principal_idselect s.hostname from sys.sysprocesses s group by s.hostnameselect
-- schema_name(o.schema_id) as 'Schema',
-- o.type,
o.name,
m.definition
from sys.sql_modules m
join sys.objects o on o.object_id = m.object_id
where
(
--o.type in ('AF', 'FN', 'P' ) and
m.definition like '%SomeTableName%' escape '\' -- find something in the definition
and o.name like '%v2%' -- only where names are like this
)
Goselect *
from information_schema.routines
where
(
routine_type = 'PROCEDURE' OR
routine_type = 'FUNCTION'
)
and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_', 'p_M')SELECT
TableName = t.name,
IndexName = ind.name,
IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name,
ind.*,
ic.*,
col.*
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
ind.is_primary_key = 0
AND ind.is_unique = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id;-- From http://www.sqlservercentral.com/scripts/Miscellaneous/31456/
-- Generate temporary table to hold procedure call tree
IF OBJECT_ID('tempdb..#procdeps') IS NOT NULL DROP TABLE #procdeps
CREATE TABLE #procdeps (id_child INT, name_child NVARCHAR(128), id_parent INT, name_parent NVARCHAR(128), level INT, hierarchy VARCHAR(900))
ALTER TABLE #procdeps ADD CONSTRAINT uk_child_parent UNIQUE (id_child, id_parent)
CREATE INDEX idx_hierarchy ON #procdeps (hierarchy)
GO
BEGIN
DECLARE @proccnt INT
DECLARE @prevcnt INT
DECLARE @itercnt INT
SET NOCOUNT ON
TRUNCATE TABLE #procdeps
-- Insert all top level procedures from sysdepends into tree table (all that are not listed as children of relationships)
-- Initialize level and path (needed for calculating relationships afterwards)
PRINT 'Generating procedure tree ... ' + CHAR(13) + CHAR(10) + 'Inserting top level procedures ...'
INSERT INTO #procdeps
SELECT obj.id, obj.name, NULL, NULL, 0, '.' + CAST(obj.id AS VARCHAR) + '.'
FROM sysobjects obj
WHERE obj.xtype = 'P' OR obj.xtype = 'AF' OR obj.xtype = 'FN'
AND OBJECTPROPERTY(obj.id, 'ismsshipped') = 0
AND obj.id NOT IN (
SELECT depid
FROM sysdepends)
-- Insert all dependent procedures into tree table
PRINT 'Inserting dependent procedures ...'
INSERT INTO #procdeps
SELECT obj2.id, obj2.name, obj1.id, obj1.name, NULL, NULL
FROM sysobjects obj1,
sysobjects obj2,
sysdepends dep
WHERE obj1.id = dep.id
AND obj1.xtype = 'P'
AND OBJECTPROPERTY(obj1.id, 'ismsshipped') = 0
AND obj2.id = dep.depid
AND obj2.xtype = 'P'
AND OBJECTPROPERTY(obj2.id, 'ismsshipped') = 0
-- Repeat until all relationships are calculated (or a cycle is detected)
PRINT 'Calculating relationships ...'
SET @itercnt = 0
SET @prevcnt = 0
SELECT @proccnt = COUNT(1) FROM #procdeps WHERE hierarchy IS NULL
WHILE @proccnt > 0 AND @prevcnt <> @proccnt BEGIN -- Run 10 iterations at max
PRINT 'Iteration ' + CAST(@itercnt + 1 AS VARCHAR) + ' - ' + CAST(@proccnt AS VARCHAR) + ' Dependencies to calculate ...'
-- Node gets level of parent + 1 (top level node gets 0)
-- Node appends its id to path of parent (all ids delimited by dots, top level node gets just its id)
-- Top level case is not needed here (only used if statement should calculate dependency for single rows iteratively)
UPDATE child
SET level = CASE
WHEN child.id_parent IS NULL THEN 0
ELSE parent.level + 1
END,
hierarchy = CASE
WHEN child.id_parent IS NULL THEN '.'
ELSE parent.hierarchy
END + CAST(child.id_child AS VARCHAR) + '.'
FROM #procdeps child LEFT OUTER JOIN
#procdeps parent ON child.id_parent = parent.id_child
-- Count iteration and check if missing procedures
-- If count of procedures without hierarchy does not change between iterations a cycle is detected
SET @prevcnt = @proccnt
SET @itercnt = @itercnt + 1
SELECT @proccnt = COUNT(1) FROM #procdeps WHERE hierarchy IS NULL
END
IF @proccnt = @prevcnt
PRINT 'Finished (cycles detected) ...'
ELSE
PRINT 'Finished ...'
PRINT CHAR(13) + CHAR(10)
END
-- Select hierarchical dependencies as pseudo graphical tree view
PRINT 'Procedure hierarchy ...'
SELECT CAST(CASE
WHEN level = 0 THEN name_child
ELSE REPLICATE(' | ', level) + name_child
END AS NVARCHAR(256)) proctree
FROM #procdeps
WHERE hierarchy IS NOT NULL
ORDER BY hierarchy
GO
-- Select procedures with cyclic call graph
PRINT 'Cyclic dependencies ...'
SELECT CAST(name_child + ' -> ' + name_parent AS NVARCHAR(256)) proctree
FROM #procdeps
WHERE hierarchy IS NULL
ORDER BY hierarchy
GOuse database
SELECT TOP 1000 o.name, i.type_desc, o.type_desc, o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND i.type_desc = 'HEAP'
ORDER BY o.name
GOThe script simply runs select from sys.databases statement on the enumerated servers using SQLCMD client.
REM Save this script in a .bat file and run the bat file
@echo off
REM ^ allows to continue string on next line. The LAST line should not have ^ at the end.
REM %% escapes %
set dblist="D:\Data\Tools\Quick Launch\Database Cheat sheet.txt"
set databaseNamesToInclude=name not in ('master', 'model', 'msdb', 'tempdb') ^
and lower(name) not like '%%test%%' ^
and lower(name) not like 'qa%%'
set serverlist=servername1.domain.com ^
servername2.domain.com ^
servername3.domain.com
echo & echo Refreshed at %DATE% %TIME% > %dblist%
echo & echo. >> %dblist%
FOR %%i IN (%serverlist%) DO (
echo ** %%i ** >> %dblist%
SQLCMD /S %%i /E /d master -h -1 /Q"set nocount on SELECT rtrim(name) FROM sys.databases where %databaseNamesToInclude%" >> %dblist%
REM -h -1 removes the hyphens and headers from the query output.
REM state=6 represents offline databases.
REM & echo. prints new line character.
echo & echo. >> %dblist%
)
echo onbcp "select top 10 col1, col2 from dbo.SomeTable" queryout Input.txt -T -S ServerName -cselect max_workers_count from sys.dm_os_sys_infoselect s.hostname as Hostname, count(*) as NumberOfConnections from sys.sysprocesses s group by s.hostname order by NumberOfConnections desc