SQL Server
How do I find a stored procedure containing keyword?
SELECT OBJECT_NAME(id) as object_name
FROM SYSCOMMENTS
WHERE [text] LIKE '%keyword%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
NOTE: The below works great if your stored procedures are under 4000 characters.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%keyword%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY LAST_ALTERED DESC
How do I loop through temp variable?
DECLARE @tmpTable as TABLE (ID int, processed int)
INSERT INTO @tmpTable
SELECT ID, 0
FROM table123
DECLARE @tID int
WHILE (SELECT COUNT(*) FROM @tmpTable WHERE processed = 0) > 0
BEGIN
SET @tID = 0
SELECT TOP 1
@tID = ID
FROM @tmpTable
WHERE processed = 0
--do something with @tID
UPDATE @tmpTable
SET processed = 1
WHERE ID = @tID
END
How do I loop through temp variable #2?
DECLARE @xmlZipCodes xml
SET @xmlZipCodes = '<zips><zp cd="55306"/><zp cd="55543"/></'zips>
--create temp table
DECLARE @tmpZipTable as TABLE (idx smallint Primary Key IDENTITY(1,1), ZipCode varchar(20))
--populate temp table with incoming xml data
INSERT INTO @tmpZipTable
SELECT e.value('@cd', 'varchar(20)') as ZipCode
FROM @xmlZipCodes.nodes('//zp') tblCandidates(e)
DECLARE @i int
DECLARE @numrows int
DECLARE @tmp_ZipCode varchar(100)
--set variables
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @tmpZipTable)
IF @numrows > 0
--enumerate the table
WHILE (@i <= (SELECT MAX(idx) FROM @tmpZipTable))
BEGIN
--get next candidate_email
SET @tmp_ZipCode = (SELECT ZipCode FROM @tmpZipTable WHERE idx = @i)
--do something with variable
print @tmp_ZipCode
--increment counter
SET @i = @i + 1
END
Create sql based on field found in table.
--Create SQL to show tables with JobPosting_ID AND Client_ID
SELECT 'SELECT * FROM ' + name + ' WHERE Client_ID = @Client_ID AND JobPosting_ID = @JobPosting_ID'
FROM sys.tables
WHERE object_id IN ( SELECT id FROM syscolumns WHERE name = 'jobposting_id' )
AND object_id in
( SELECT id FROM syscolumns WHERE name = 'client_id' )
ORDER BY name
Change the schema of a database object
--TABLES
SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oldschemaname'
Change the schema of a database object
--Stored procedures, table, functions
SELECT 'ALTER SCHEMA dbo TRANSFER ' + SysSchemas.Name + '.' + DbObjects.Name + ';'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'oldschemaname'
AND (DbObjects.Type IN ('U', 'P', 'V', 'FN'))
See table schema name(s)
SELECT s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE t.name = N'BidTool';
Change the schema of a table
ALTER SCHEMA dbo TRANSFER [db_owner].BidTool;
Stored procedure that is asp/aspx helper
-- =============================================
-- Author:
-- Create date: 2016-04-05
-- Description: ASP helper sp
-- USAGE: EXEC asp_Helper 'tablename'
-- =============================================
CREATE PROCEDURE [dbo].[asp_Helper]
-- Add the parameters for the stored procedure here
@TABLE_NAME varchar(55)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select 'sqlCmd.Parameters.Add(New SqlParameter("@' + COLUMN_NAME + '", SqlDbType.' + data_type + ', ' + cast(isnull(character_maximum_length, '') as varchar(10)) + ')).Value = ' + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where table_name = @TABLE_NAME
order by ordinal_position
select '@' + COLUMN_NAME + ' ' + case WHEN DATA_TYPE = 'varchar' THEN 'varchar(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(CHARACTER_MAXIMUM_LENGTH as varchar(25)) END + ')'
WHEN DATA_TYPE = 'int' THEN 'int'
WHEN DATA_TYPE = 'datetime' THEN 'datetime' END + ','
from INFORMATION_SCHEMA.COLUMNS
where table_name = @TABLE_NAME
order by ordinal_position
select '@' + COLUMN_NAME + ','
from INFORMATION_SCHEMA.COLUMNS
where table_name = @TABLE_NAME
order by ordinal_position
select 'da.SelectCommand.Parameters.Add("@' + COLUMN_NAME + '", SqlDbType.' + data_type + case WHEN isnull(character_maximum_length, 0) > 0 THEN ', ' + cast(isnull(character_maximum_length, 0) as varchar(10)) ELSE '' END + ').Value = ' + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where table_name = @TABLE_NAME
order by ordinal_position
select case WHEN IS_NULLABLE = 'YES' THEN 'Optional ' ELSE '' END + 'ByVal ' + COLUMN_NAME + ' ' + case WHEN DATA_TYPE = 'varchar' THEN ' As String ' + case WHEN IS_NULLABLE = 'YES' THEN '= ""' ELSE '' END + ', _'
WHEN DATA_TYPE = 'int' THEN ' As Integer ' + case WHEN IS_NULLABLE = 'YES' THEN '= 0' ELSE '' END + ', _'
WHEN DATA_TYPE = 'datetime' THEN 'As String' + case WHEN IS_NULLABLE = 'YES' THEN '= ""' ELSE '' END + ', _' END
from INFORMATION_SCHEMA.COLUMNS
where table_name = @TABLE_NAME
order by ordinal_position
END
Missing Index Script
-- Original Author: Pinal Dave (C) 2011
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
View database size
SELECT file_id, name, type_desc, physical_name, (size*8)/1024 as size_MB, size, (max_size*8)/1024 as max_size_MB, max_size
FROM sys.database_files
View database size left
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files
View table sizes
SELECT
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' -- filter out system tables for diagramming
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
s.Name, t.Name
Resize Log File
Get log file name
SELECT @log_File_Name = name FROM sysfiles WHERE filename LIKE '%LDF'
Shink the log file to 1 MB
DBCC SHRINKFILE (N'namehere_log' , 1)
Might have to do 'SET RECOVERY SIMPLE' / 'SET RECOVERY FULL' before and after DBCC call
Finding Time Query takes in stored procedures
DECLARE @_StartTime datetime
DECLARE @_EndTime datetime
SET @_StartTime = getdate()
-------------
-- QUERY 1
-------------
SELECT * FROM TABLE1
SET @_EndTime = getdate()
print 'Timecheck: Query 1 Results: ' + CAST(CAST(@_EndTime - @_StartTime as TIME) as VARCHAR(100))
SET @_StartTime = getdate()
-------------
-- QUERY 2
-------------
SELECT * FROM TABLE2
SET @_EndTime = getdate()
print 'Timecheck: Query 2 Results: ' + CAST(CAST(@_EndTime - @_StartTime as TIME) as VARCHAR(100))
How to identify the most costly SQL Server queries using DMV’s
--John Sansom: https://www.johnsansom.com/how-to-identify-the-most-costly-sql-server-queries-using-dmvs/
SELECT TOP 20
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS Total_CPU,
total_CPU_inSeconds = --Converted from microseconds
qs.total_worker_time/1000000,
average_CPU_inSeconds = --Converted from microseconds
(qs.total_worker_time/1000000) / qs.execution_count,
qs.total_elapsed_time,
total_elapsed_time_inSeconds = --Converted from microseconds
qs.total_elapsed_time/1000000,
st.text,
qp.query_plan
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY
qs.total_worker_time DESC
See running queries
-- Saadat: In the fields of status, wait_type and cpu_time you can find the most cpu consuming task that is running right now.
SELECT s.session_id,
r.status,
r.blocking_session_id 'Blk by',
r.wait_type,
wait_resource,
r.wait_time / (1000 * 60) 'Wait M',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc