Skip to main content

Posts

Showing posts from August, 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

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

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