MSSQL - Some Useful SQL Scripts and Tips

-- get a folder list using xp_cmdshell

CREATE TABLE #Temp(FileName VARCHAR(255))

DECLARE @SQL VARCHAR(1000)

SET @SQL = 'dir "C:\Temp\*.xls" /b'

INSERT INTO #Temp(FileName) EXEC Master..xp_cmdShell @SQL

SELECT * FROM #Temp WHERE FileName IS NOT NULL

-- get a folder list using xp_dirtree

CREATE TABLE #Temp(FileName VARCHAR(255), Depth INT, FileFlag INT)

INSERT INTO #Temp EXEC xp_dirtree 'C:\Temp', 1, 1

SELECT * FROM #Temp

-- find a column name conatins some string

SELECT

     t.name AS table_name

    ,SCHEMA_NAME(schema_id) AS schema_name

    ,c.name AS column_name

FROM sys.tables AS t

JOIN sys.columns c

  ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%ppp%'

ORDER BY schema_name, table_name;

-- check existence of a table

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'NewTableName')

  PRINT 'Not Exists'

-- check existence of a column in a table

IF NOT EXISTS (SELECT * FROM SYS.COLUMNS where Name = N'NewColumnName' AND Object_ID = Object_ID(N'TableName'))

  PRINT 'Not Exists'

begin

    ALTER TABLE BT_CustomerConfiguration

    ADD UTF VARCHAR(10)

End

-- Create store procedure if not exist already

IF OBJECT_ID('dbo.USP_MyStoredProcedure') IS NULL

EXEC('CREATE PROCEDURE dbo.USP_MyStoredProcedure  AS DUMMY:')

ALTER PROCEDURE

-- Cursor Template

DECLARE @tablename VARCHAR(50), SQL VARCHAR(2000)

DECLARE Mycursor CURSOR FOR SELECT Table_name FROM information_schema.tables where TABLE_SCHEMA = 'lp' order by 1

OPEN Mycursor

FETCH NEXT FROM Mycursor  INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

   SELECT @tablename

   FETCH NEXT FROM Mycursor  INTO @tablename

END

CLOSE Mycursor

DEALLOCATE Mycursor

--Creates a DB with default settings if not exist

USE master;

go

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TestDBAudit')

    DROP DATABASE TestDBAudit;

go

CREATE DATABASE TestDBAudit;

Go

-- drop temp table

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL EXEC('DROP TABLE #temp1')

-- create function which returns a table

ALTER FUNCTION FUNC_LP_GetHistoricCashSummary(@CompanyNum VARCHAR(2), @LeaseNumList VARCHAR(MAX))

RETURNS TABLE

AS

RETURN

(

…..

)

-- user account maintenance

SET QUOTED_IDENTIFIER OFF

GO

-- Remove user from each database where the account exists

EXEC sp_Msforeachdb "IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MYDOMAIN\UserName')

                     DROP USER [MYDOMAIN\UserName]"

GO

-- Remove login from this SQL Server

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYDOMAIN\UserName') DROP LOGIN [EPGPDOM\tJanowich] 

GO 

-- Event logging from SQL script

EXEC master..xp_logevent 90000, '###0###', 'ERROR'

-- Check and Delete temp table

IF OBJECT_ID('tempdb..##TEMP_AP') IS NOT NULL EXEC('DROP TABLE ##TEMP_AP')

-- Check and Delete existing table

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#Temp_Table') EXEC('DROP TABLE #Temp_Table')

-- List all running processes for the default database

DECLARE @DatabaseName VARCHAR(50) = DB_NAME()

SELECT * FROM MASTER.dbo.SysProcesses WHERE DBId = DB_ID(@DatabaseName)

-- List all running processes for the default database

SELECT sqltext.TEXT,

req.session_id,

req.status,

req.command,

req.cpu_time,

req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

KILL [session_id]

-- List all blocked processes

SELECT

     spid,

     status,

     loginame=SUBSTRING(loginame,1,12),

     hostname=SUBSTRING(hostname,1, 12),

     blk = CONVERT(char(3), blocked),

     dbname=SUBSTRING(DB_NAME(dbid),1, 10),

     cmd,

     waittype

FROM master.dbo.sysprocesses

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

-- Check job status

Exec msdb..sp_help_job @job_name = 'Job Name'

--Check for "current_execution_status", if its "4", the job is not running, if "1" its executing.

-- drop all statistics

SELECT 'EXEC (''DROP STATISTICS [' + su.name + '].[' + so.name + '].[' + si.name + ']'')'

FROM sysindexes si

JOIN sysobjects so

ON si.id = so.id

JOIN sysusers su

on su.uid = so.uid

WHERE INDEXPROPERTY(si.id,si.name, 'IsStatistics') = 1

--AND si.name not like '_WA_Sys%'

AND OBJECTPROPERTY(so.id, 'IsUserTable') = 1

ORDER BY so.name, si.name

-- Read and sort Columns

DECLARE @TBLName nvarchar(128)

SET @TBLName = N'MyTable'

SELECT name, object_id FROM sys.tables WHERE name = @TBLName

SELECT name, column_id, object_id

FROM sys.columns

WHERE object_id IN

     (SELECT object_id

     FROM sys.tables

     WHERE name = @TBLName)

order by name

-- using VALUES to construct a temp table

SELECT col1, col2

FROM (VALUES ('January', 1),('Feburary', 2),('March', 3),('April', 4),('May', 5)) AS tbl(col1, col2)

WHERE tbl.col1 = 'January'

-- SQL to truncate all tables

Select DISTINCT 'TRUNCATE TABLE [' + TABLE_NAME + ']'

From INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME like 'LPlus%'

-- Chaneg NVARCHAR to VARCHAR for all tables

Select 'Alter Table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] Alter Column [' + COLUMN_NAME + '] VarChar(' + CAST(CHARACTER_MAXIMUM_LENGTH As VARCHAR) + ')'

From INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE = 'NVARCHAR'

-- Display all running SQL jobs

SELECT sj.name

   , sja.*

FROM msdb.dbo.sysjobactivity AS sja

INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id

WHERE sja.start_execution_date IS NOT NULL

   AND sja.stop_execution_date IS NULL

-- Checking completion of DB recovery

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time

FROM sys.dm_exec_requests

order by 1 desc

-- Loopback SQL Call

if exists (select * from master..sysservers where srvname = 'loopback')

    exec sp_dropserver 'loopback'

go

exec sp_addlinkedserver @server = N'loopback',

    @srvproduct = N'',

    @provider = N'SQLOLEDB',

    @datasrc = @@servername

go

select * into #t from openquery(loopback, 'exec yourSproc')

select * from #t

drop table #t

go

-- update SQL Statistics

sp_MSForEachDB @command1 = "use ?;exec sp_updatestats"

-- Create Views that mirror another databases tables

SELECT

'create view '+tbl.name+' as select * from plus.dbo.['+tbl.name++']'+char(10)+'go'+char(10)

FROM

sys.tables AS tbl

WHERE

(CAST(

 case

    when tbl.is_ms_shipped = 1 then 1

    when (

        select

            major_id

        from

            sys.extended_properties

        where

            major_id = tbl.object_id and

            minor_id = 0 and

            class = 1 and

            name = N'microsoft_database_tools_support')

        is not null then 1

    else 0

end         

             AS bit)=0)

ORDER BY [Name] ASC