Find Specific word or phrase from all stored procedure, views, and triggers available in SQL Server 2005/2008

We may require sometime to find specific TableName or some words or some phrase have been used in any SP, Triggers or in VIEW. This is interesting to know how we can do it with T-SQL rather than manually checking script of everything.

Before I move forward to my original script, let me give you some basic logic behind that. Actually SYSObjects contains Name, ID along with many useful information of objects like stored procedure, view, trigger, function, user table, system table etc and SysComments contain ID of objects along with Text which has been used to create that object. So, now it may have popped up in your mind that I must have joined these two system views.

Before I show you the script I also would like to introduce you with the possible values could be used in “XType” column of “SysObjects” as that is the base and you can decide what to look (SP, Triggers, Views or anything else)

Here are some of the values of “Xtype” column and its meaning.

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

So now you are all set to look at the script, have a look at it.

–display the list of any trigger, view and stored procedure which has used ‘SY’ in the script

SELECT DISTINCT so.name,sc.text

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE so.xtype in (‘P’,‘TR’,‘V’) and sc.TEXT LIKE ‘%sy%’

order by name

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Advertisements

Keep log of Update in same table with Instead Of Trigger in SQL Server 2005

I wrote few articles on audit trails and would prefer to keep log in separate table even I see many times how to keep update track records in same table so I thought to create one example and share it with my readers.

CREATE TABLE STATSofTable

(

Status INT NOT NULL,

LOGS VARCHAR(MAX)

)

–insert into stats table

INSERT INTO STATSofTable (status,LOGS)

SELECT 1,‘TEST’ UNION ALL

SELECT 2,‘TEST’ UNION ALL

SELECT 3,‘TEST’

–CREATE TRIGGER TO UPDATE LOG AUTOMATICALLY

CREATE TRIGGER AuditTrailOnSTATSofTable ON STATSofTable

INSTEAD OF UPDATE

AS

SET NOCOUNT ON

BEGIN

IF UPDATE(Status)

BEGIN

DECLARE @OldValue VARCHAR(1)

DECLARE @NewValue VARCHAR(1)

DECLARE @OldLog VARCHAR(max)

DECLARE @NewLog VARCHAR(max)

SELECT @OldValue = Status FROM Deleted

SELECT @NewValue = Status FROM INSERTED

SELECT @OldLog= LOGS from Deleted

SET @NewLog=@OldLog + ‘ STATUS CHANGE FROM ‘ + @OldValue + ‘ TO ‘+ @NewValue + ‘ ON ‘ + CONVERT(VARCHAR(50), getdate())

UPDATE STATS SET Status=@NewValue, LOGS=@NewLog WHERE Status=@OldValue

print @oldvalue

print @newvalue

print @OldLog

END

END

–UPDATE STATUS

update STATSofTable set status=4 where status =1

update STATSofTable set status=1 where status =4

select * from STATSofTable

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Auditing Trail with Trigger in SQL Server 2005

If you are new to this concept in database world than you might think what is Auditing Trail? The answer is pretty much easy and simple, if you want to increase the data-integrity and wants to have full details about insertion, edition and deletion of your data; Audit Trail is the concept you have to adopt.

Since this is a very huge topic and not possible to cover complete topic in one or may be in few articles, I will give some basic details here and will write some more article with live situation as and when time permits.

You would like to keep track of total update and delete in records after its insert, you may want which front-end accessed your row for modification, which user has changed which records.

There are many methods popular to keep track of your records change like you can have duplicate table for each table along with some comment field sand keep the track and old value. You may wish to create one table which will have details off all tables and about every single transaction but In that case, maintenance of that table is very crucial.

Let us create one table and try to keep trail of database.

–demo table for AuditTrail

–Note: this is just for demo purpose, in live situation we may have very big table than
— on explained here. everybody has their own need and situation.
CREATE TABLE AuditTrail(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT def_AuditTrail_Id DEFAULT(NEWID()),
AuditDate DATETIME,
TableName VARCHAR(50) NOT NULL,

ColumnName VARCHAR(50) NOT NULL,

Description VARCHAR(50) NOT NULL,

UserName VARCHAR(50) NOT NULL,

OldValue VARCHAR(50) NOT NULL,

NewValue VARCHAR(50) NOT NULL

)


–Now we will create one more table for which we will keep the trail in above table
–You can keep Audit of Insert, Delete and Update everything in above table

–and can customize it as per your need

CREATE
TABLE AuditDemo
(

ID INT IDENTITY(1,1) NOT NULL,

Name
VARCHAR(10),

Company VARCHAR(10)


— have few records in above table

INSERT INTO AuditDemo
SELECT
‘Ritesh’,‘eChem’ UNION ALL

SELECT ‘Rajan’,‘Marwadi’

–LET us create fixed audit trail trigger
create
TRIGGER AUDITonAuditDemo
ON
AuditDemo
AFTER UPDATE

AS

IF
UPDATE(Name)
BEGIN

INSERT
AuditTrail

(
AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)

SELECT

GETDATE
(),‘AuditDemo’,‘Name’,‘Update’,suser_sname(),DELETED.Name,INSERTED.Name

FROM
INSERTED

JOIN
DELETED

ON
INSERTED.ID=DELETED.ID

END

IF UPDATE(company)
BEGIN

INSERT
AuditTrail

(
AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)

SELECT

GETDATE
(),‘AuditDemo’,‘Company’,‘Update’,suser_sname(),Deleted.Company,Inserted.Company

FROM
INSERTED

JOIN
DELETED

ON
INSERTED.ID=DELETED.ID

END


–let us update record in AuditDemo tabel

UPDATE AuditDemo SET Name=‘R.Shah’ WHERE ID=2

–LET US NOW CHECK WHETHER WE HAVE TRAILED THE RECORDS OR NOT

SELECT * FROM AuditTrail


–let use update and check once agian

UPDATE AuditDemo SET Company=‘Testing’

SELECT * FROM AuditTrail

Hope you have enjoyed!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

UPDATE() function with Trigger in SQL Server 2005:

UPDATE() function in Trigger will is one of the very useful function related to trigger. UPDATE() function will help you to know that which column is going to update. Let us see how will it work?

–create table1 for demo

CREATE TABLE CustInfo

(

CustId INT Identity(1,1),

Name VARCHAR(10),

Country Varchar(10),

)

GO

–insert records in above table

INSERT INTO CustInfo

SELECT ‘RITSEH’,’India’ UNION ALL

SELECT ‘RAJAN’ ,’Hindustan’

GO

–CREATE trigger to check which field

–we are updating

CREATE TRIGGER UpdateCheck ON CustInfo

AFTER UPDATE

AS

IF UPDATE(Name)

BEGIN

PRINT ‘YOU HAVE UPDATED NAME FIELD’

END

ELSE

BEGIN

PRINT ‘YOU HAVE NOT UPDATED NAME FIELD’

END

–check this out

UPDATE CustInfo SET Name =‘R.Shah’ WHERE Name=‘RAJAN’

As we are updating Name field, we will see first message as condition will fall in IF part.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Instead Of Trigger in SQL Server 2005

As name said “Instead Of” when you submit any transaction, “Instead of” trigger get fire first instead of your submitted DML processes further. You can define only one “Instead Of” Trigger for one table or one view.

–create table for demo

CREATE TABLE empData

(

Name varchar(10),

Age INT

)

–create INSTEAD OF trigger on empData table,

–which will show message if Age > 100

–and roll back transaction

alter TRIGGER empAgeCheck ON empData

INSTEAD OF INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

ROLLBACK

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–finally check your data in empData table.

select * from empData

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

After Trigger in Microsoft SQL Server 2005 (CREATE and ALTER)

After giving quiet a good details about Triggers in my previous article, I would like to move further and want to give introduction about After Trigger in SQL Server.

AFTER Trigger was the only trigger before Microsoft SQL Server 2000 and it is useful as well. Table can contain more than one AFTER trigger. You can use AFTER trigger for recording data audit trails, complex business rule and for complex data validation.

AFTER trigger fire after all transaction gets complete respected to DML command but before COMMIT.

–create table for demo

CREATE TABLE empData

(

Name varchar(10),

Age INT

)

–create AFTER trigger on empData table,

–which will show message if Age > 100

–but won’t stop inserting records.

CREATE TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–if you want to abort the batch

–if it break the rule than ALTER your TRIGGER

–and add rollback in it

ALTER TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

ROLLBACK

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–finally check your data in empData table.

select * from empData

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Triggers in Microsoft SQL Server

Triggers are nothing but the special stored procedure which used to fire automatically with table events like INSERT, UPDATE, and DELETE. Apart from traditional Instead of and after trigger, Microsoft SQL Server 2005 became rich with DDL Triggers. You can have a look at DDL Triggers at:

http://www.sqlhub.com/2009/03/ddl-trigger-in-sql-server-2005-for.html

The main reason now I am writing Trigger article is, one should have concept of transaction flow, T-SQL, Stored Procedure of SQL Server etc. I finished many articles for said topic and felt this is the right time to introduce Triggers in my blog.

If you are aware with ORACLE than you might know that ORACLE used to fire trigger per operation and per row but SQL Server fires up the trigger once for every data modification not for affected row only. This is the thing I like most about SQL Server’s trigger.

As per my first paragraph, SQL Server has two types of DML trigger available Instead of trigger and after trigger. Both are completely different than each other in every aspect.

Before we start writing the trigger practically, it would be interesting and useful to understand when and how trigger will fire? To understand this, let us see transaction flow of SQL Server.

— Identity insert always gets priority
— Nullability Constraint
— Data-type check
–DML command like INSERT, UPDATE, and DELETE stops execution and Instead Of trigger will fire
— Primary-Key constraints
— Check Constraints
— Foreign-Key constraints
— Hold DML finishes
— After Trigger
— COMMIT trans
— finally writes the data file.

In short, when you executes any DML statement, it will first check Identity, nullability and data type check than performs Instead Of trigger. Once SQL Server done with these, it will run After Trigger before committing the transaction.

I will come up with example of triggers in my next articles.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com