The following function returns the Nth occurance of week day in a month IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME =’GetNthWeekday’ AND ROUTINE_SCHEMA = ‘dbo’ AND ROUTINE_TYPE=’FUNCTION’)DROP FUNCTION dbo.GetNthWeekdayGOCREATE FUNCTION dbo.GetNthWeekday(@date datetime,–Any date of month under consideration@N int,– Nth…
Finding the DDL Triggers in a database
Below is a method to list out DDL triggers which are created on your database SELECT * FROM sys.triggers WHERE parent_class=0 Please note that DDL triggers are not schema scoped and hence their information wont be available in system catalog…
Generating Calendar Table
Recently I’ve seen couple of posts where posters asks for requirement for generating day by day reports like sales analysis report showing all day info. In such cases, we might need to generate a calendar table on the fly to…
Parsing a delimited string
Most often I’ve seen posts where the requirement is to parse a delimited string and convert it to table of values. Below is a table valued function which I use to parse a delimited string and convert it to table…
Export to excel from SQL report showing cached data
I have recently encountered a problem in SSRS report. I was basically trying to export to excel from my report. First time I rendered report for a set of parameters and export to excel it worked fine. But subsequent rendering…
Find identity columns in a database
Here’s a way to find out the identity columns in a db/table SQL 2000 SELECT c.TABLE_NAME ,c.COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNS cWHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,’IsIdentity’) = 1 SQL 2005/2008 SELECT OBJECT_NAME(Object_Id) AS Table_name, Name AS COLUMN_NAME FROM sys.identity_columns for getting identity columns in table just…
Cross server cross db object dependencies in SQL 2008
Finding cross server cross database object dependencies has become easier with SQL 2008. The new catalog view sys.sql_expression_dependencies it is now possible to find the entities that are dependent on other entity across server or across databases in same server.…
SELECT .. INTO table with IDENTITY function
Most often I’ve come across scenarios where we need to generate a unique valued column on the fly. From SQL 2005 onwards, we have the window functions like ROW_NUMBER() for doing this however for earlier versions one typical solution I’ve…
Some quick tips regarding datetime values
Until SQL 2008 we dont have a mechanism to store either date or time values alone. The only data type available is datetime which involves date as well as time part. Hence a common requirement that we face is strip…
Inscope operator in SQL Reporting Services
One of the very handy operator I’ve come across in SQL Reporting services is Inscope operator. Quite often we come across scenarios where we need to generate dynamic crosstabbing reports based on particular field data. One of most commonly used…