Tuesday, August 30, 2011

List ALL triggers within a Database

In SQL Server 2005 and above, we can make use of SYS.TRIGGERS to list all the Triggers within a database. The below query would help us list the trigger name and the table name on which this trigger is written.

SELECT
[name] AS Trigger_Name,
OBJECT_NAME(Parent_ID)
FROM
SYS.TRIGGERS
ORDER BY
Trigger_Name

Sunday, August 28, 2011

Search a specific column within ALL tables

This stored procedure takes two parameters:

1. Column name to be searched within all tables
2. string/value which needs to be searched within all tables.

CREATE PROCEDURE usp_SearchAllTables_InExistingDB
(
@colName varchar(100),
@colValue varchar(1000)
)
As
BEGIN

-- Copyright © 2011 Vadivel Mohanakrishnan. All rights reserved.
-- Purpose: To search a particular column of all tables for a given search string
-- Written by: Vadivel Mohanakrishnan
-- Site: http://vadivel.blogspot.com
-- Tested on: SQL Server 2005

SET NOCOUNT ON

--VARIABLE DECLARATION
Declare @TableName nvarchar(100)
Declare @ColumnName nvarchar(100)
Declare @RowNum int
Declare @searchString varchar(1000)
Declare @columnToSearch varchar(100)

--ASSIGN SP PARAMETER VALUE TO LOCAL VARIABLE
Set @searchString = @colValue
Set @ColumnToSearch = @colName

--TEMP TABLE TO HOLD THE FINAL RESULT!
CREATE TABLE #SearchResults
(
TableName VARCHAR(1000),
RecordCount INT
)

--TEMP TABLE TO HOLD THE TABLENAMES CONTAINING
-- COLUMNNAME WE ARE LOOKING FOR!
CREATE TABLE #TableList
(
rownum INT IDENTITY(1,1),
Table_name VARCHAR(100),
schema_name VARCHAR(100),
column_name VARCHAR(100)
)

--CHAR, VARCHAR, NCHAR, NVARCHAR ARE ONLY PICKED
Insert into #TableList
SELECT
t.name AS table_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
c.name AS column_name
FROM
sys.tables AS t INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
JOIN sys.types AS tp ON c.user_type_id=tp.user_type_id
WHERE
tp.name in ('char', 'varchar', 'nchar', 'nvarchar')
and c.name LIKE @ColumnToSearch
ORDER BY schema_name, table_name


Select @RowNum = Min(RowNum) from #TableList

WHILE @RowNum is not null
Begin

SELECT
@TableName = MIN(QUOTENAME(Table_Name)),
@ColumnName = MIN(QUOTENAME(Column_Name))
FROM #TableList
where RowNum = @RowNum
SELECT @RowNum = MIN(RowNum) FROM #TableList
WHERE RowNum > @RowNum

INSERT INTO #SearchResults
EXEC
(
'SELECT ''' + @TableName + ''', COUNT(' + @ColumnName + ')
FROM ' + @TableName + ' WITH (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @searchString
)
End

--RESULT IS SHOWN HERE
SELECT TableName, RecordCount FROM #SearchResults

--Cleanup
DROP TABLE #TableList
DROP TABLE #SearchResults
END
GO

To test:

Exec usp_SearchAllTables_InExistingDB 'CityID', '25'

Saturday, August 20, 2011

Adding current datetime to your DB backup filename

DECLARE @Dbname VARCHAR(20)
DECLARE @Date DATETIME
DECLARE @test VARCHAR(30)

SET @Dbname = 'Test'
SET @Date = GETDATE()
SET @test = 'c:\test' + @Dbname

SELECT @test = @test + CONVERT(VARCHAR(10), @Date, 112) + '.bak'
BACKUP DATABASE @Dbname TO DISK = @test

Database Backup from Command prompt

We can make use of "SQLCMD" to execute scripts from Command prompt. If you are new to it I would suggest to go over my introductory posts on them here.

Example 1: Writing a batch file with the TSQL script to backup a DB.

Save the below sample script as a batch file (lets say, DBBackup.Bat).

echo off
cls

set /p DBNAME=Enter database name:
set BACKUP=c:\%DBNAME%.bak
set SQLSERVERNAME=Enter_Your_SQL_ServerName_here

echo.

sqlcmd -E -S %SQLSERVERNAME% -d master -Q "BACKUP DATABASE [%DBNAME%] TO DISK = N'%BACKUP%'"

echo.
pause

We can now go to the SQL Command prompt by typing SQLCMD in Start > Run dialogue box. On executing DBBackup.Bat it would prompt us to enter the name of the database which has to be backed up. The backup file would be stored in C:\ drive. This is just a sample which can be modified or extended according to our need.

Just in case we have already have a Stored procedure to backup the DB for us. Then we can still make use of that SP directly within the Batch file.

Example 2: Calling a .SQL file within the batchfile to do the DB backup.

Save the below script as a SQL file. Say, "Backup.SQL"

/*
Have provided the simpler script for demo purpose. You can get the DB name, backup name as parameters as well. And convert this into a solid Stored proc.

Change TESTDW with your database name.
*/

BACKUP DATABASE TESTDW TO DISK = N'c:\testdw.bak'
GO

Now the batch file DBBackup.BAT can be modified as follows:

echo off
cls

echo.

sqlcmd -E -S Your_ServerName -d master -i "C:\Backup.sql"

echo.
pause


In both the samples above I have used trusted connection. If you want to pass username and password change the SQLCMD script to use -U and -P as shown below instead of -E:

sqlcmd -S Your_ServerName -UyourUserName -PyourPwd -d master -i "C:\Backup.sql"

Friday, August 19, 2011

Convert an 8 digit integer into hhmmss format

Declare @intSeconds int
Set @intSeconds = 99999999

SELECT
Total_Duration = CONVERT( VARCHAR, RIGHT('00000' + RTRIM(@intSeconds/3600), 5)
+ 'hrs ' + RIGHT('00' + RTRIM((@intSeconds % 3600) / 60), 2)
+ 'mins ' + RIGHT('00' + RTRIM((@intSeconds % 3600) % 60), 2)
+ 'secs ')