Normally, trying to insert data into an identity column (autoincremental column) results in an error message. However, there are cases when we need to do this, and using SET IDENTITY_INERT, it is possible. Here is how: SET IDENTITY_INSERT MyTable ON INSERT INTO MyTable(MyTableId, …) VALUES (42, …) SET IDENTITY_INSERT MyTable OFF Here is the message […]
How to compare dates without taking the time into account
Today, I had to fix a bug in a stored procedure that was not handling dates correctly. A date column was being compared to the current date. However, the current date also contains hour, minute and second values whereas the time part of the date column always contained 00:00:00. In other words, we did not […]
How to keep track of database structure changes
Here is an interesting method for keeping track of modifications brought to an SQL Server database structure: http://www.codeproject.com/KB/database/sql2005dba.aspx This is quite useful when working on a new version of a database driven web site, since all modifications made to the development database will eventually have to be applied to the QA database and finally the […]
Search SQL objects
Sometimes, I wished I had a mean to find database objects using a particular column or table, and I just came across an interesting script that might help with this. It is a stored procedure to find SQL Server objects based on a column name or text in the code (for Stored Procedures, Functions and […]
SQL Server query to list all user tables in a database
select name from sysobjects where type = ‘U’