Normally to find out whether or not a trigger is enabled/disabled, we can simply run this:
SELECT OBJECTPROPERTYEX (OBJECT_ID(‘TriggerName’),’ExecIsTriggerDisabled’)
A DDL trigger, however, is not schema-scoped, therefore the above query will return NULL. To find out, here’s the query that you need to run:
SELECT name, is_disabled
FROM sys.triggers
WHERE name = ‘TriggerName’
Archive for the ‘SQL Server’ Category
How To Find Out If A DDL Trigger Is Enabled
Posted in SQL Server, tagged tips, trigger on 30 November 2009 | Leave a Comment »
How to Create An Empty Database
Posted in SQL Server, tagged schema on 4 November 2009 | Leave a Comment »
Not a new database, but an empty database – no data, but it still preserves the existing schema. You have choices:
1. Use a combination of Truncate/Delete
USE MyDB
EXEC sp_msforeachtable ‘print ‘’TRUNCATE TABLE ?’’’
The command will truncate all tables in the database, EXCEPT when a column in a table has a constraint such as a foreign key. [...]
How to Convert Cursor Procedure Into Set-based Query T-SQL
Posted in SQL Server, tagged tips, tsql on 24 September 2009 | 2 Comments »
Cursors have been taught as a no-no in SQL programming, mostly because it is resource-intensive. Of course, there will be some exceptional cases where the use of cursors is justified. In my case, I used cursors because it feels familiar. If you have learnt some procedural ‘ancient’ programming languages, you would understand what I [...]
One-liner : Find out the node/ server name
Posted in SQL Server, tagged one-liner, tsql on 21 September 2009 | Leave a Comment »
In SQL Server 2000:
select * from ::fn_virtualservernodes()
In SQL Server 2005:
select * from sys.dm_os_cluster_nodes
Or this backward compatibility function:
select * from fn_virtualservernodes()
Reference: BOL
One-liner: sp_change_users_login
Posted in SQL Server, tagged one-liner, tsql on 18 September 2009 | Leave a Comment »
To automatically map users to the SQL logins on the server:
exec sp_change_users_login ‘Auto_fix’
To find orphaned users ( users with no logins):
Use
exec sp_change_users_login @Action = ‘Report’
Reference: BOL
How to Rename Your Database – SQL Server 2005
Posted in SQL Server, tagged modify, tips on 14 September 2009 | Leave a Comment »
In SQL Server 2000, you can easily rename your database running sp_renamedb. The syntax from BOL as follows:
sp_renamedb [ @dbname = ] ‘old_name’ , [ @newname = ] ‘new_name’
In SQL Server 2005, however, this has changed. You will need to use ALTER DATABASE MODIFY NAME instead.
ALTER DATABASE database_name
MODIFY NAME = new_database_name
Although the database [...]
Find Out Tables With No Primary Key/Index – SQL Server 2005
Posted in SQL Server, tagged diagnostic, performance, tips on 11 August 2009 | Leave a Comment »
I read the other day about indexing small tables I’d like to test this on my database but first I would need to find out which tables do not have an index and/or a primary key.
Pinal Dave wrote about finding tables without a clustered index or a primary key:
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE [...]
Querying sp_who2
Posted in SQL Server, tagged tips, tsql on 7 August 2009 | Leave a Comment »
Wouldn’t you wish sometimes you can just query the sp_who2 results? The only way to do this is to create a temporary table to dump the result, and then query the table. You can use SELECT statement as per normal.
CREATE TABLE #who
(SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) [...]
Uninstall SQL Server 2005 Express Instance
Posted in SQL Server, tagged administration, installation on 5 August 2009 | Leave a Comment »
Following my previous post on how to uninstall SQL Server 2008 instance, this time I am writing about how to uninstall the SQL Express 2005. Here’s the thing: I have already had a SQL Server 2005 Developer instance when I installed the SQL Express 2005. I can’t tell apart the double-installed SQL Server 2005, so [...]
How to Find Out the Members of a Group
Posted in SQL Server, Windows, tagged security, Windows group on 27 July 2009 | Leave a Comment »
There are times when you want to know who the users are in a particular group. Depending on your level of privileges, there are various ways to get this information:
1. You can find out about it using the SQL Server Management Studio or Query Analyzer:
EXEC xp_logininfo ‘DOMAIN\group_name’,’members’
To run this, you will need to have [...]
