SQL | Suhail Kaleem - Part 3

SQL

Find Duplicate Entries Of A Particular Column In A Table

0

SELECT col1, COUNT(col1) AS NumOccurrences FROM table1 GROUP BY col1 HAVING (COUNT(col1) > 1)

Replace col1  with your column name  and

table 1 with your table name

Find Longest Running Query

0

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO

Following DBCC commands clears the cache of the server and starts fresh logging of the query running time.

DBCC FREEPROCCACHE

Change ownership of sql table

0

This sql script will change the ownership of table to dbo

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
@old = ‘oldOwner_CHANGE_THIS’
, @new = ‘dbo’
, @sql = ‘
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+”.”+QUOTENAME(TABLE_NAME ) = ”?”
AND TABLE_SCHEMA = ”’ + @old + ”’
)
EXECUTE sp_changeobjectowner ”?”, ”’ + @new + ””

EXECUTE sp_MSforeachtable @sql

To change owner for single table use

ALTER SCHEMA NewOwner TRANSFER CurrentOwner.TableName;

Go to Top