How to find out what triggers you have.

I try to avoid using triggers because I always forget they are there.  I have been burned more than once by triggers updating data when I didn’t know they were there.  I have also been troubleshooting someone else’s database and been bitten by triggers.
The code from this post I copied from Chris McGowan’s blog.  He wrote up a great article about trigger status.  His post has code to create a job to email you.
I modified it to just run in SSMS and use a table variable (in case you don’t have the permissions to create a temp table).

This script runs against master and will tell you about all triggers in all databases.

Use master

-- Create table variable
DECLARE @TriggerStatus as Table
(
      DatabaseName SYSNAME,
      TableName VARCHAR(255), 
      TriggerName VARCHAR(255),
      TriggerStatus VARCHAR(8)
);
-- Insert triggers
INSERT INTO @TriggerStatus
EXEC sp_msforeachdb
'
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''reportserver'', ''reportservertempdb'')
BEGIN
USE [?]; 
SELECT  DB_NAME() AS DatabaseName,
        OBJECT_NAME(parent_id) AS TableName,
        name AS TriggerName,
        CASE is_disabled
          WHEN 0 THEN ''Enabled''
          ELSE ''Disabled''
        END AS TriggerStatus
FROM    sys.triggers WITH ( NOLOCK )
WHERE   is_ms_shipped = 0
        AND parent_class = 1;
END'

SELECT DatabaseName, TableName, TriggerName,  TriggerStatus FROM   @TriggerStatus 

[tweetmeme only_single=”false”]

How to disable all Foreign Key constraints

Here is a script that will loop through all tables in your database and disable the foreign key constraints.

DECLARE @TableName nvarchar(256)
SET @TableName = ''

WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped' ) = 0)

    IF (@TableName <> '')
    BEGIN
        EXEC ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
        PRINT ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
    END

END
GO
[tweetmeme only_single=”false”]

How to insert records to a table with an identity column

If you have a table with an identity column, you can’t just do a simple insert.  That identity column needs to be excluded from your insert statement.

There are times when you need to keep the ID values during your insert.  Like moving data between environments.

Here is the SQL you need to allow your inserts to work.
First turn on identity_insert

SET IDENTITY_INSERT <TABLENAME> ON;

Then do your insert.

Then turn off identity_insert

SET IDENTITY_INSERT <TABLENAME> OFF;
[tweetmeme only_single=”false”]

Instead of Triggers

A weird feature in SQL Server is the INSTEAD OF trigger.  With this trigger you can divert the database to do something other than what the user/application wanted.  You can have INSTEAD OF triggers for insert/update/delete.

Where would this be useful?  I’ve had application that have to be changed, but the source code is no longer around (had a client with this problem).  The business wanted change what data was written to the database, but they could change the application.  So I added an INSTEAT OF trigger to check the data before it was inserted/updated and have the corrected values inserted/updated instead.

Here is a real simple example.  A test table is created and loaded with one record.  An insert INSTEAD OF trigger is added.  This trigger just returns, so the insert never happens.  No matter what INSERT statement the user/application tries it just gets ignored.

CREATE TABLE Test (
      TestID            int IDENTITY(1,1) NOT NULL
,     Name        varchar(64)             NULL
,   CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestID] ASC) 
)
GO

INSERT INTO Test (Name)
VALUES ('Hello World')
GO

SELECT *
  FROM dbo.Test
GO

CREATE TRIGGER tiTest ON Test INSTEAD OF INSERT
AS
BEGIN
      RETURN
END
GO

INSERT INTO Test (Name)
VALUES ('Good Bye World')
GO

SELECT *
  FROM dbo.Test
GO

So you can see here the before and after of the Insert statement.  It just gets ignored.

image

 

Here are a couple of msdn articles (using and designing)  about INSTEAD OF triggers if you want more info.

[tweetmeme only_single=”false”]

Simple SQL best practices

It’s real easy to go crazy with best practices and then have them become so big no one follows them.
Here are a just a couple of quick best practices I follow.

1.Whenever you need to recreate a database object use DROP/ CREATE rather than ALTER.
The reason is that ALTER doesn’t modify the object’s create date time stamp.
This makes it harder to trouble shoot when you can’t figure out what has changed recently.
(See this post for how to tell what’s been modified recently.)

2. Adhere to a naming conventions of procedures (uspNAME), views (uvNAME), data/server links (dlNAME/slNAME – crossing servers or databases).
Since code can be in different places in management studio it helps to figure out what bucket you need to look at for your offending code.

[tweetmeme only_single=”false”]

Undocumented stored procedure for looping through all tables in a database

I got tired of writing code to loop through all tables in a database.  I found out about an undocumented stored procedure that does most of that for me.
The stored procedure name is sp_MSforeachtable it takes one input parameter (@command).
This simplifies so much code (no more looping through sysobjects).

Here are some examples of how to use it.

This command will return a row count for each table in your database.

EXEC sp_MSforeachtable ‘SELECT ‘‘?’‘, Count(*) as NumberOfRows FROM ?’

Notice the question mark (?), that gets substituted as the table (in the sp).
So basically any command you can do against a table you can use and put a ? where you put a table name.

Here is how to rebuild all indexes.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

Here is how you update all statistics on your tables.

EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

You can also add additional conditional logic if you need to restrict your commands to a subset of tables.
This is very powerful. You don’t always need to hit all tables with a command.

This one will loop through all your tables and add the Column CreatedOn on any table that doesn’t already have it.

EXEC sp_MSforeachtable '
    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = ''?'' and column_name = ''CreatedOn'') 
    begin
        ALTER TABLE [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
    end
'
[tweetmeme only_single=”false”]

Updated – Search for string across all fields in all tables in a database

This is an update to a previous post.

It has been updated to use table variables instead of temp tables.
This helps when you are in an environment (like production) where you don’t have permissions to create/drop temp tables.

DECLARE @Results TABLE (ColumnName nvarchar(370),   ColumnValue nvarchar(3630))

DECLARE @SearchStr nvarchar(100)

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''
SET @SearchStr = 'STRING TO SEARCH FOR'

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = '' SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped' ) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results

Track Changes to objects in your database

Ever have the need to be able to track changes to the objects in your database? This code sample was provided by a friend, Josh Shilling.  It provides you with a change log on your database.  This will be helpful in multi developer environments when you think someone made a change to your database, but you don’t know what changes were made.

So you can run Select * from dbo.ChangeLog to see what changes have been to your db objects.

I had a hard time getting the image of the results to show.

image

GO
/****** Object: Table [DBVersion].[ChangeLog] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[ChangeLog](
[LogId] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL DEFAULT (getutcdate()),
[LoginName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY
KEY NONCLUSTERED
(
[LogId] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
--Trigger
GO
/****** Object: DdlTrigger [ddltrg_ObjectRevisionHistory] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TRIGGER [ddltrg_ObjectRevisionHistory]
ON
database
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE
, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_INDEX
, ALTER_INDEX, DROP_INDEX, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_STATISTICS
, DROP_STATISTICS, UPDATE_STATISTICS, CREATE_SCHEMA, ALTER_SCHEMA,
DROP_SCHEMA
, GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE, CREATE_USER, ALTER_USER,
DROP_USER
, CREATE_VIEW, ALTER_VIEW, DROP_VIEW
as
BEGIN
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dbo.ChangeLog
(databasename, eventtype, objectname, objecttype, sqlcommand, loginname)
VALUES
(
@data
.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data
.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data
.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data
.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data
.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data
.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
END
GO
SET
ANSI_NULLS OFF
GO
SET
QUOTED_IDENTIFIER OFF
GO
ENABLE
TRIGGER [ddltrg_ObjectRevisionHistory] ON DATABASE

Search across all fields in all tables in SQL server

I found this script that allows you to search across all fields in all tables of a database for a specific string.

It’s helpful when you trying to where data might be stored in a strange/large/new database that you are not intimately familiar with.  Being a consultant I have found myself digging though a clients database many times trying to figure how the data is stored and this has saved me from looking in every table manually.

I did not come up with this code myself, but I didn’t save the link of the blog where I found it.
So unfortunately I can’t give credit to the smart person who came up with it.
To use it just set the value of @SearchStr (highlighted in red) to the string you wish to search for.

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
DECLARE @SearchStr nvarchar(100)
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr = 'string to search for' 
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
DROP TABLE #Results
Technorati Tags: ,,