Which tables have a trigger/s?

I have been recently helping my fried on very large database ,which has hundred od tables to determine which table has a trigger. So I came out with the below script that may help others to identify triggers define on the tables.

--SQL Server 2000(SP3)

SELECT
name
, CASE WHEN instrig <> 0 then OBJECT_NAME (instrig) ELSE 'None' END as
'Insert Trigger'
, CASE WHEN updtrig <> 0 then OBJECT_NAME (updtrig) ELSE 'None' END as
'Update Trigger'
, CASE WHEN deltrig <> 0 then OBJECT_NAME (deltrig) ELSE 'None' END as
'Delete Trigger'
FROM
sysobjects
WHERE
type = 'U'
AND
( instrig <> 0
OR
updtrig <> 0
OR
deltrig <> 0
)

What's the version of SQL Server?

I have seen this question (see post's subject) so many times in newsgroup asking people, so I decided to post some usefull info about SQL Server by using SERVERPROPERTY command.

SELECT @@VERSION AS [Server Information]

SELECT SERVERPROPERTY('ServerName') AS [ServerName]
SELECT SERVERPROPERTY('MachineName') AS [MachineName]
SELECT SERVERPROPERTY('InstanceName') AS [InstanceName]

SELECT SERVERPROPERTY('productversion') AS [ProductVersion]
SELECT SERVERPROPERTY('ProductLevel') AS [ProductLevel]
SELECT SERVERPROPERTY('Edition') AS [Edition]

SELECT SERVERPROPERTY('Collation') AS [Collation]
SELECT SERVERPROPERTY('IsClustered') AS [IsClustered]
SELECT SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled]
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS
[IsIntegratedSecurityOnly]
SELECT SERVERPROPERTY('LicenseType') AS [LicenseType]
SELECT SERVERPROPERTY('NumLicenses') AS [NumLicenses]

Mixed or Windows Authentication Only

Ok, at this time I would to discuss about the one but realy painful issue we have to face where we use SQL Authentication (Mixed).
Consider the following situation. You are using SQL Server (I speak for SQL Server 2000 SP4) Authentication(Mixed).
You also have a database and user within which is mapped to the SQL login. Now , you have been told to move the database on the new server. So you created a backup of the database , copied it to the new server as well as create a SQL Login with the same name as you had on the old one. Ok, now it is time to perform restore database, so everything went just fine but when theh users tried to connnect to the database (through application) on the new server they get the error. What happened? The thing is that SQL Server is 'losing' SIDs that mapped between a login and user when you created a new SQL Login on the new server (master..syslogins) and a database user (dbname..sysuser)that you brought within a database. So we have sp_change_users_login system stored procedure that takes care of re-mapping SID's.

However, that DOES NOT happen if you use Windows Authentication Only.That means SQL Server keeps SID's realtionship between Windows Login and User database although you move the database on the new server.

Just one more argument to use Windows Authentication Only.

In SQL Server 2005 SP2 MS has introduced some greate features on subject.
http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx.

Drop all indexes

Well, sometimes we have to do that. A large INSERT statement may run faster for instance. The below script just generate DROP command of all indexes in current dataabse. You can add a filter to specify a table name for example.(SQL Server 2000)

SELECT
'DROP INDEX ' +
QUOTENAME(USER_NAME(o.uid)) +
'.' +
QUOTENAME(o.name) +
'.' +
QUOTENAME(i.name)
FROM sysobjects o
JOIN sysindexes i ON
i.id = o.id
WHERE i.indid BETWEEN 1 AND 254 AND
INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 0 AND
INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 0 AND
OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

Some third parties break DIFFERENTIAL backup chains

I'd like to share some things I have seen on my client's machine. He has a full backup database set up once a night and every four hours differential back during the work day. But at the same time ( it should be done at night) the network administrator has ran tird party software backup to copy .MDF/LDF files. When we started to restore database it throws the error that SQL Server cannot apply this bakup file to the full backup file created at night. We did lots of testing on the developing machine and did not het the error. And only when we asked the net.admin not to run this software then we were able to restore the database succefully.

Note, I have not seen that doing LOG files backup does break the chains.

So please do lots of testing before you find your self without properly created disaster recovery strategy.


I found that MS has this KB explaining the behaviour.
http://support.microsoft.com/kb/903643.