Contents
1.2 Qry Running Process with Object and sqltext
1.3 get currently executing query
1.5 Qry Currently running qry Statistics
1.6 sys.dm_exec_query_stats with sys.dm_exec_sql_text
1.7 sys.dm_exec_query_plan with sys.dm_exec_sql_text
2.2 Qry Get all Indexes in a DB - sys.indexes with sys.objects
3 RegEx_Like - USE AdventureWorks2012
3.1 % Any string with zero or more characters in the search pattern
3.2 Any single character search with the specified pattern -- Using SQL LIKE with the ‘_’
3.3 [] Any single character search within the specified range
3.4 [^] Any single character search not within the specified range
3.5 --finds all the persons where the first name column has more than 3 characters.
3.7 identify the rows has (999-999-9999) pattern
3.8 Using SQL LIKE with dynamic SQL
3.9 Using SQL Like with an IF statement
Running Process
Qry Long Running Process
select spid
,(waittime / (1000*60*60)) as Wt_Time_Hrs
--,waittime
,dbid
,login_time
,last_batch
,status
,cmd
,loginame
from master.dbo.sysprocesses where (waittime / (1000*60*60)) > 100
order by waittime desc
Qry Running Process with Object and sqltext
SELECT
sysprc.spid,sysprc.waittime,sysprc.lastwaittype,
DB_NAME(sysprc.dbid) AS database_name,sysprc.cpu,
sysprc.physical_io,sysprc.login_time,sysprc.last_batch,
sysprc.status,sysprc.hostname,sysprc.[program_name],sysprc.cmd,
sysprc.loginame,OBJECT_NAME(sqltxt.objectid) AS [object_name],sqltxt.text
FROM sys.sysprocesses sysprc
OUTER APPLY sys.dm_exec_sql_text(sysprc.sql_handle) sqltxt
get currently executing query
SELECT t.[text]
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
FOR XML RAW, ELEMENTS;
About Executing Qry
SELECT
CASE
WHEN statement_end_offset = -1
THEN text
ELSE SUBSTRING(text,statement_start_offset/2,(statement_end_offset- statement_start_offset)/2)
END, *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
Qry Currently running qry Statistics
SELECT DISTINCT
DB.NAME as "DB"
, ST.name as "Table"
,USER_SEEKS
,USER_SCANS
,USER_LOOKUPS
,USER_UPDATES
,LAST_USER_SEEK
,LAST_USER_SCAN
,LAST_USER_LOOKUP
,LAST_USER_UPDATE
FROM
master.sys.databases DB,
SYS.TABLES ST,
SYS.DM_DB_INDEX_USAGE_STATS DMI
WHERE
DB.database_id > 4
AND DB.database_id = DMI.database_id
AND ST.object_id = DMI.object_id
AND LAST_USER_UPDATE IS NOT NULL
sys.dm_exec_query_stats with sys.dm_exec_sql_text
SELECT TOP 50
QueryState.execution_count
,OBJECT_NAME(objectid)
,query_text = SUBSTRING(
qt.text,
QueryState.statement_start_offset/2,
(CASE WHEN QueryState.statement_end_offset = -1
THEN len(convert(nvarchar(max), qt.text)) * 2
ELSE QueryState.statement_end_offset
END - QueryState.statement_start_offset)/2)
,qt.dbid
,dbname = db_name(qt.dbid)
,qt.objectid
FROM sys.dm_exec_query_stats QueryState
CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) as qt
ORDER BY QueryState.execution_count DESC
sys.dm_exec_query_plan with sys.dm_exec_sql_text
SELECT
SUBSTRING(dest.text, ( deqs.statement_start_offset / 2 ) + 1,
( CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset- deqs.statement_start_offset
END ) / 2 + 1) AS querystatement ,
deqp.query_plan ,deqs.execution_count ,
deqs.total_worker_time ,deqs.total_logical_reads ,deqs.total_elapsed_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
where total_elapsed_time > 100000
sp_who2
Available Memory
SELECT total_physical_memory_kb/1024 as "Total Memory MB",
available_physical_memory_kb/1024 as "Available Memory MB",
available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free"
FROM sys.dm_os_sys_memory
Databases
sys.databases
select
@@SERVERNAME as Instance
,name -- DB name
,database_id -- upto 5 sysdbs from 6 user dbs
,create_date
,compatibility_level
,recovery_model_desc
,collation_name
FROM master.sys.databases
where database_id > 5
Qry Get all Indexes in a DB - sys.indexes with sys.objects
select
idx.[name] as [Index]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
where idx.[name] is not null and obj.[type] = 'u'
order by idx.[name]
RegEx_Like - USE AdventureWorks2012
% Any string with zero or more characters in the search pattern
-- % - Any string with zero or more characters in the search pattern
SELECT TOP 10 *
FROM Person.Person
WHERE firstname LIKE 'A%';
GO
Any single character search with the specified pattern -- Using SQL LIKE with the ‘_’
SELECT p.FirstName,
p.LastName,
PhoneNumber
FROM Person.PersonPhone AS ph
INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '7_8%'
ORDER BY p.LastName;
[] Any single character search within the specified range
SELECT p.FirstName,
p.LastName,
PhoneNumber
FROM Person.PersonPhone AS ph
INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '7_8%' and p.lastname like 'Ba[ik]%'
ORDER BY p.LastName;
[^] Any single character search not within the specified range
Lists the last names not from a to e ie disply from F
SELECT p.FirstName,
p.LastName
FROM Person.Person p
WHERE LastName LIKE '[^a-e]%'
ORDER BY p.lastname;
Lists the last names ONLY from a to e
SELECT p.FirstName,
p.LastName
FROM Person.Person p
WHERE LastName not LIKE '[^a-e]%'
ORDER BY p.lastname;
--finds all the persons where the first name column has more than 3 characters.
SELECT DISTINCT
firstname
FROM Person.Person
WHERE firstname NOT LIKE '[a-z][a-z][a-z]';
Lists last name only with 3 characters
SELECT DISTINCT
firstname
FROM Person.Person
WHERE firstname LIKE '[a-z][a-z][a-z]';
escape the character
the ESCAPE clause is used to escape the character ‘!’ to negate the meaning of ‘%’ to find the string ‘100% Free’ in the column col1 of the temp table
DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp(col1 VARCHAR(100));
GO
INSERT INTO #temp
VALUES('ApexSQL Refactor is 100% Free SQL Formatter tool'), ('ApexSQL Backup is 10-15% off today only');
GO
SELECT *
FROM #temp;
like with % as escapte
SELECT *
FROM #temp
WHERE col1 LIKE '%100!% Free%' ESCAPE '!';
SELECT *
FROM #temp
WHERE col1 LIKE '%100!%%' ESCAPE '!';
GO
identify the rows has (999-999-9999) pattern
SELECT p.FirstName,
p.LastName,
PhoneNumber,
CASE WHEN ph.PhoneNumber LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
then 'Domestic Phone Number'
ELSE 'International Phone number'
END Location --PhoneNumber
FROM Person.PersonPhone AS ph
INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
ORDER BY p.LastName;
Using SQL LIKE with dynamic SQL
DECLARE @ELastName VARCHAR(20)= 'rb';
SELECT p.FirstName,
p.LastName,
a.City
FROM Person.Person p
JOIN Person.Address a ON p.BusinessEntityID = a.AddressID
WHERE p.LastName LIKE '%'+@ELastName+'%';
Using SQL Like with an IF statement
DECLARE @RuleName NVARCHAR(MAX)= 'SQL Sever 2019 CTP is available for preview';
IF @RuleName LIKE 'SQL Sever [0-9]% CTP is available for preview'
PRINT 'valid input good!';
ELSE
PRINT 'not a valid good!';
zz
No comments:
Post a Comment