Vadivel's blog

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

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

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

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

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