-- 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
