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’
Posted in SQL Server | Tagged tips, trigger | 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. This will complicate matters because SQL Server will throw an error: ’Cannot truncate due to foreign key constraint’ or something similar. Depending on how big is the table, I usually run a DELETE with no WHERE clause. It achieves the same result, although you should keep an eye on your log, making sure you still have some space to move around.
2. Clone the existing database
A better method of creating an empty database is by creating a clone for an existing database. You create a database creation scripts by right-clicking on the target database>Tasks>Generate Scripts. Follow the prompts. Don’t use the default value as they may not include options that you need. Please refer to the Microsoft article http://support.microsoft.com/kb/914288/en-us for more information.
Posted in SQL Server | Tagged schema | Leave a Comment »
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 meant.
However, RBarryYoung’s article part 1 and part 2 , convinced me to shake off my dependency to cursors.
Here are the basic steps to convert a cursor:
1. Use the SELECT Clause of the Declare Cursor statement as the basis for our table source. For Selects and Inserts, we may be able to use the SELECT as-is. For Updates and Deletes, we will probably use the From and Where clauses.
2. Any Set or Select statements in the While loop will become columns outputs.
3. Any Containing IF statement, within the loop but containing everything else, will be added to our Where clause.
4. Any IF statements around a single line will become a CASE function in column outputs
And I might add another step here:
5. If there are more than 1 SELECT statements under different DECLARE CURSOR, then it is a possible JOIN.
I successfully converted some of my stored procedures to set-based and it does make a difference in the total transaction cost. Why don’t you give it a try?
Posted in SQL Server | Tagged tips, tsql | 2 Comments »
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
Posted in SQL Server | Tagged one-liner, tsql | 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
Posted in SQL Server | Tagged one-liner, tsql | 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 name has changed, you probably realise that the logical and physical database filename are still the same! What it means is if you create another database with the old name at the same server with the same filename, you will get an error.
My favourite way to deal with it is to detach and attach the database.
– execute the sp to detach a database
exec sp_detach_db ‘TestDB’,'true’– rename the physical file to whatever you want
– execute attach database and specify filenames.
EXEC sp_attach_db @dbname = ‘TestDB’,
@filename1 = ‘C:\SQLData\TestDB.MDF’,
@filename2 = ‘C:\SQLLog\TestDB_Log.LDF’
I realise that some people have a better way of doing this. If so, you’re welcome to share your thought!
Posted in SQL Server | Tagged modify, tips | 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 INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,’IsUserTable’) = 1
ORDER BY [TABLE]
GO
I tweaked it a bit to include all indexes:
SELECT OBJECT_NAME (object_id) as TableName, name as IndexName, type_desc as IndexType
FROM sys.indexes
WHERE OBJECTPROPERTY(OBJECT_ID,’IsUserTable’) = 1
Some of the results shows that the table has some nonclustered indexes but the primary key is not clustered. For example:
TableName IndexName IndexType
Codes NULL HEAP
Codes PK_Codes NONCLUSTERED
Codes IX_Codes NONCLUSTERED
I know now what I need to do next!
A more sophisticated version from Jason Strate can be found here. It shows all the tables with heaps with some statistics.
Posted in SQL Server | Tagged diagnostic, performance, tips | 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) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT,
REQUESTID INT)
GO
INSERT INTO #who
EXEC sp_who2
GO
SELECT DBName, Login, Status FROM #who
WHERE DBName = ‘master’
GO
DROP TABLE #who
GO
Posted in SQL Server | Tagged tips, tsql | 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 do I do it?
Believe it or not, Microsoft website suggests you to uninstall the SQL Express via Add/Remove Program. I feel rather apprehensive toward this method as so many things can go wrong and you’ll end up spending more time cleaning up after the mess.
Instead of doing that, I go to C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe. The following window will pop up:
Choose the SQL Express instance
There you have SQL Server 2005 instances available and you can simply select the one that you want to uninstall. Click next and follow through. After the check up, another window will pop up.
Click on Remove Microsoft SQL Server and this will uninstall the installation of SQL Express on your computer. You can check it by running the ARPWrapper.exe again.
Posted in SQL Server | Tagged administration, installation | 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 a sysadmin or a public access in the master database with EXECUTE permission granted. For more information, see BOL – xp_logininfo
2. You can also do this using the command prompt:
cmd> net group “group_name” /domain
Will yield the same result as above.
Drill down a user by using the net user command:
cmd> net user user_name /domain
3. GUI: Administrative Tools> Active Directory Users and Computers
Right click on your domain and select Find. Type in the group name (without the domain) and click Find Now. Double click on the result. Select Members tab to see the list of users in that group.
Posted in SQL Server, Windows | Tagged security, Windows group | Leave a Comment »
