SQL SERVER: Creating Comma Separate Values List from Table Column

There are two ways to generate a comma separated list from a table column. First is to manually concatenate the result and the second that is more sophisticated is to use the built-in function COALESCE of SQL Server.

Example Problem:
For example you have a table named COUNTRY that has a column named NAME, and you want to generate a comma separated list for countries.

Solution 1:
This is the simple but not the smartest way to implement it:

DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + ISNULL(NAME,'') + ','
FROM COUNTRIES
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

Solution 2:
This is the smartest way:

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + NAME
FROM COUNTRIES
SELECT @listStr

SQL Server: To check all the advanced configuration of the SQL Server

Run the following database script to check all the advanced configuration options

EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;

SQL: UDF to convert the seconds into MM:SS

-- =============================================
-- Author:Sabah u din Irfan
-- Create date: July/1/2009
-- Description: A UDF to convert the seconds into MM:SS
-- Example: select [dbo].[fn_SEC2MIN](36)
-- print dbo.fn_SEC2MIN ( 36045)
-- =============================================
CREATE FUNCTION [dbo].[fn_SEC2MIN]( @Sec as int )
RETURNS VARCHAR (15)
AS
BEGIN


DECLARE @return AS VARCHAR (15)
DECLARE @i_MM as INT
DECLARE @i_SS as INT

SET @i_MM=0
SET @i_SS=0
SET @return = '00:00'

IF ( @SEC >= 60 )
BEGIN
SET @i_MM = FLOOR(@Sec / 60 )
SET @i_SS = @Sec % 60
END
ELSE
BEGIN
SET @i_SS = @Sec

END
SET @return = case len(cast( @i_MM AS Varchar)) when 1 then right('00' + cast( @i_MM AS Varchar), 2 ) else cast( @i_MM AS Varchar) end +':'+ right('00' + cast( @i_SS AS Varchar), 2 )

RETURN @return

END

SQL: UDF to convert the seconds into HH:MM:SS

-- =============================================
-- Author: Sabah u din Irfan
-- Description: A UDF to convert the seconds into HH:MM:SS
-- Example: SELECT fn_SEC2HHMMSS(65)
-- =============================================
CREATE FUNCTION [dbo].[fn_SEC2HHMMSS]( @sec as int )
RETURNS VARCHAR (15)
AS
BEGIN


RETURN
case len(convert(varchar(15),@sec/3600))
when 1
then RIGHT('00'+convert(varchar(5),@sec/3600),2)
else convert(varchar(15),@sec/3600)
end
+':'+RIGHT('0'+convert(varchar(5),@sec%3600/60),2)
+':'+RIGHT('0'+convert(varchar(5),(@sec%60)),2)


END

SQL Server: WAITFOR TIME Statement

WAITFOR TIME SQL statement is used to set the particular time to execute the next query/SQL statement.

For Example the second query will execute when the particular time will be reached:

DECLARE @MyDateTime DATETIME
/* Add 5 seconds to current time so
system waits for 15 seconds*/
SET @MyDateTime = DATEADD(s,15,GETDATE())
SELECT GETDATE() CurrentTime
WAITFOR TIME @MyDateTime
SELECT GETDATE() CurrentTime

SQL Server: WAITFOR DELAY Statement to set Delay in Queries

WAITFOR DELAY statement is used in T-SQL to set the delay time between the SQL queries. For example the second query will execute after 10 seconds of delay:

SELECT GETDATE() CurrentTime
WAITFOR DELAY ‘00:00:10′ —- 10 Second Delay
SELECT GETDATE() CurrentTime

SQL Server: Lising all the Databses Names and Their Size

Following stored procedure lists all of the data bases names along with their sizes and remarks/

exec sp_databases

Column name	Data type	Description
DATABASE_NAME	sysname	Name of the database. In the Database Engine, this column represents the database name as stored in the sys.databases catalog view.
DATABASE_SIZE	int	Size of database, in kilobytes.
REMARKS	varchar(254)	For the Database Engine, this field always returns NULL.

SQL Server 2005: Using NULLIF

It takes two parametes like,
NULLIF (param1,param2)
and returns NULL if both param1 and param2 are equal.

NULLIF returns the first parameter if the two parameters are not equivalent

NOTE:
param1 and param2 can be a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.

NULLIF is somehow similar to CASE function.


http://www.bloglines.com/blog/sabah-irfan?id=41

SQL Server 2005: Using NULLIF

It takes two parametes like,
NULLIF (param1,param2)
and returns NULL if both param1 and param2 are equal.

NULLIF returns the first parameter if the two parameters are not equivalent

NOTE:
param1 and param2 can be a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.

NULLIF is somehow similar to CASE function.


http://www.bloglines.com/blog/sabah-irfan?id=41

SQL Server @ To List The Database Names along with their Size

The following system stored procedure is helpful to list the all database names along with their size in KiloBytes:

EXEC sp_databases

Results:
--------------------------------
DataBase_Name DataBase_Size REMARKS
HighFall_Rockin 3712 NULL
master 4608 NULL
model 1728 NULL
msdb 7360 NULL
tempdb 19456 NULL
TLSPFX 4689152 NULL
TLSPFX_BK 987008 NULL

SQL Server @ Query To List Available Triggers On a DataBase

The following query is helpful to enlist all the available triggers on a specific database. sys.triggers is a view that contains a row for each object that is a trigger.

Here is the query:

USE MSDB -- Database Name Here
SELECT name,type,type_desc,create_date,modify_date
FROM sys.triggers


Results:
---------------------------------------------------------
name type type_desc create_date modify_date

trig_sysmail_profile_delete TR SQL_TRIGGER 2005-10-14 01:55:32.520 2005-10-14 02:02:31.787
trig_sysmail_servertype TR SQL_TRIGGER 2005-10-14 01:55:32.740 2005-10-14 02:02:31.850
trig_sysmail_server TR SQL_TRIGGER 2005-10-14 01:55:33.067 2005-10-14 02:02:31.910
trig_sysmail_configuration TR SQL_TRIGGER 2005-10-14 01:55:33.287 2005-10-14 02:02:31.943
trig_sysmail_mailitems TR SQL_TRIGGER 2005-10-14 01:55:33.507 2005-10-14 02:02:31.990
trig_backupset_delete TR SQL_TRIGGER 2005-10-14 01:55:16.113 2005-10-14 02:02:32.007
trig_sysmail_attachments TR SQL_TRIGGER 2005-10-14 01:55:33.723 2005-10-14 02:02:32.050
trig_sysmail_log TR SQL_TRIGGER 2005-10-14 01:55:33.943 2005-10-14 02:02:32.100
trig_sysoriginatingservers_delete TR SQL_TRIGGER 2005-10-14 01:54:09.833 2005-10-14 02:02:32.397
trig_sysjobs_insert_update TR SQL_TRIGGER 2005-10-14 01:54:10.490 2005-10-14 02:02:32.520
trig_sysschedules_insert_update TR SQL_TRIGGER 2005-10-14 01:54:12.677 2005-10-14 02:02:33.210
trig_targetserver_insert TR SQL_TRIGGER 2005-10-14 01:55:08.570 2005-10-14 02:02:34.740
trig_notification_ins_or_upd TR SQL_TRIGGER 2005-10-14 01:55:14.583 2005-10-14 02:02:35.333
trig_notification_delete TR SQL_TRIGGER 2005-10-14 01:55:14.910 2005-10-14 02:02:35.363
trig_sysmail_profile TR SQL_TRIGGER 2005-10-14 01:55:31.647 2005-10-14 02:02:35.787
trig_principalprofile TR SQL_TRIGGER 2005-10-14 01:55:31.867 2005-10-14 02:02:35.833
trig_sysmail_account TR SQL_TRIGGER 2005-10-14 01:55:32.083 2005-10-14 02:02:35.863
trig_sysmail_profileaccount TR SQL_TRIGGER 2005-10-14 01:55:32.303 2005-10-14 02:02:35.910

SQL Server @ Query To List The available SQL JOBS

The following query is helpful to enlist the name, created date, modified date and description of the Sql Jobs available. You can add the mre column in the result set if needed.

USE MSDB

SELECT name,enabled,date_created,date_modified,description
FROM sysjobs

Results:
-------------------------------------------------------------
Name Enabled Date Created Date Modified
GEServiceEntries 1 2007-04-11 11:25:49.897 2007-04-11 11:54:05.503
CDW_CORFXSQL01_CORFXSQL01_0 1 2006-02-28 19:51:08.967 2006-02-28 19:51:09.217
DataEntryHHReports 1 2006-11-03 13:25:36.230 2007-09-20 11:43:29.297

SQL Server@ Query To See the Execution History of SQL JOBS

I have made a couple of SQL jobs that are scheduled accordingly to perform some specific tasks. I wanted to know whether the jobs are executed accordingly or not? And what are the results of the execution? Means success or failure. And finally, I created a query for this purpose after looking into the MSDB. Here is it:

USE MSDB

SELECT j.name,h.run_status,h.run_date,h.run_time, h.run_duration,h.server, h.message
FROM sysjobhistory h,sysjobs j
WHERE h.job_id=j.job_id
ORDER BY run_date desc ,run_time desc

Here are the results: (NOTE: I have omitted the Message column here)
-----------------------------------------------------------------------------
DataEntryHHReports 1 20070921 121000 0 CORFXSQL01
DataEntryHHReports 1 20070921 121000 1 CORFXSQL01
DataEntryHHReports 0 20070914 121301 0 CORFXSQL01
DataEntryHHReports 2 20070914 121001 0 CORFXSQL01
DataEntryHHReports 0 20070914 121000 301 CORFXSQL01
GEServiceEntries 1 20070911 0 0 CORFXSQL01
GEServiceEntries 1 20070911 0 1 CORFXSQL01
DataEntryHHReports 1 20070907 121000 1 CORFXSQL01
DataEntryHHReports 1 20070907 121000 1 CORFXSQL01
DataEntryHHReports 1 20070831 121001 0 CORFXSQL01
DataEntryHHReports 1 20070831 121000 1 CORFXSQL01
DataEntryHHReports 1 20070824 121001 0 CORFXSQL01
DataEntryHHReports 1 20070824 121000 1 CORFXSQL01
DataEntryHHReports 1 20070817 121000 0 CORFXSQL01
DataEntryHHReports 1 20070817 121000 0 CORFXSQL01
GEServiceEntries 1 20070811 0 0 CORFXSQL01
GEServiceEntries 1 20070811 0 0 CORFXSQL01

SQL Server@ List Stored Procedure Names using specific table

Sometimes you need to drop some table or needs to modify the name of the table, and before doing that you want to make sure which are the stored procedures that are using this table?

Secondly, you just want to update the table by deleting it's some column, and you want to make sure which are the stored procedures that are using this column?

The following query gives you the list of Stored procedures that are using some specific table name "CDRMaster".

SELECT Name, create_date,modify_date
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%CDRMaster%'

The following query will give you the list of the stored procedures using some specific column name(CDRID in this case)of the table CDRMaster:

SELECT Name, create_date,modify_date
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%CDRMaster%'
AND OBJECT_DEFINITION(object_id) LIKE '%CDRID%'

SQL Server@ List Stored Procedure Names Created in Specific Date Range

This simple query returns the list of stored procedure names that are created with in the specific data range given in where clause.

SELECT Name,create_date,modify_date
FROM sys.procedures
WHERE create_date between '07/14/2007' and '08/20/2007'

Inserting rows with default values in a Table

The following simple query will insert a row into the table name specified with the default values for each column:

INSERT INTO TableName DEFAULT VALUES

UDF to Get Day of Week in SQL Server 2005

The following UDF can be used to get the Day of Week by passing it a specific date:

CREATE FUNCTION dbo.udf_Day_Of_Week(@p_Date DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @return_DayofWeek VARCHAR(10)
SELECT @return_DayofWeek = CASE DATEPART(dw,@p_Date)
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
RETURN (@return_DayofWeek)
END

How To Take Database Schema Backup in SQL Server 2005

How To Take Database Schema Backup in SQL Server 2005

• Go to OBJECT EXPLORER of the SQL Server 2005 and expand the Databases tree node, you can see the available databases in your server.

• Then Right Click on the specific Database you wish to make the schema backup. And go to TASKSà GENERATE SCRIPTS like shown in the figure below:

• After selecting the GENERATE SCRIPTS you will see a welcome screen like shown below.

• Click on NEXT button of the Wizard and you will be asked to select the database you wish to generate schema backup like below in figure(TestDB is the database in this case):

• If you want to take the full schema back up of the database then check the checkbox “Script all objects in the selected database” like shown here in the figure and click Next button of the wizard.

• In the next screen you can play with the different properties. Just need to change one of the property if you need to use this schema on SQL Server 2000 version, that is “Script for Server Version” and select the appropriate version you needed.

• After selecting the appropriate SQL Server version for which you are preparing the schema backup click Next Button and you will reach at the OUTPUT OPTION and then select the appropriate output option. In my case I am saving the script of schema in a File of Unicode Text like:

• Then Click Next and you will reach on the Script wizard Summary screen, Simply Click Finish to execute your requested job.

• After clicking the Finish button you will reach at the Generate Script progress Wizard. It will take a little time depending upon the number of objects in your selected database. After completing its job it will show you the success status.

• If you want to see the Report of the whole process, just select from the options of Report button just up from the Close and select the output option of the report. Otherwise click Close. That’s it and you are Done.

A UDF To Get Previous Working Day in SQL Server 2005

It has pretty similar logic as we done with the UDF of Next Working Day. You can read the earlier post on "How to get the Week Day" for better understanding. Here is the self explanatory code.

CREATE FUNCTION dbo.udf_GetPreviousWorkingDay (@p_Date DATETIME )
RETURNS DATETIME
AS
BEGIN
DECLARE @m_WeekDay INT
DECLARE @rt_Prev_Working_Day DATETIME
SET @m_WeekDay = DATEPART(weekday,@p_Date)

IF @m_WeekDay = 1 -- IF SUNDAY then subtract 2 fromsunday to get friday that is working day
SET @rt_Prev_Working_Day = DATEADD(d,-2,@p_Date)
ELSE IF @m_WeekDay = 2 -- IF MONDAY then subtract 3 fromsunday to get friday that is working day
SET @rt_Prev_Working_Day = DATEADD(d,-3,@p_Date)
ELSE -- ELSE subtract one to get the prev date
SET @rt_Prev_Working_Day = DATEADD(d,-1,@p_Date)

RETURN @rt_Prev_Working_Day
END

Here are some of the test cases having the same result:

SELECT dbo.udf_GetPreviousWorkingDay ( '07/21/2007')
SELECT dbo.udf_GetPreviousWorkingDay ( '07/22/2007')
SELECT dbo.udf_GetPreviousWorkingDay ( '07/23/2007')

Result:

2007-07-20 00:00:00.000

A UDF To Get The Next Working Day @ SQL Server 2005

The following scalar valued function will return the next working day of the week. Here is the code:

CREATE FUNCTION dbo.udf_GetNextWorkingDay (@p_Date DATETIME )
RETURNS DATETIME
AS
BEGIN
DECLARE @m_WeekDay INT
DECLARE @rt_Next_Working_Day DATETIME
SET @m_WeekDay = DATEPART(weekday,@p_Date)

IF @m_WeekDay = 6 -- Friday
SET @rt_Next_Working_Day = DATEADD(d,3,@p_Date)
ELSE IF @m_WeekDay = 7 -- Saturday
SET @rt_Next_Working_Day = DATEADD(d,2,@p_Date)
ELSE
SET @rt_Next_Working_Day = DATEADD(d,1,@p_Date)

RETURN @rt_Next_Working_Day
END

To test this UDF you can use the following example:

SELECT dbo.udf_GetNextWorkingDay ( '07/20/2007')
SELECT dbo.udf_GetNextWorkingDay ( '07/21/2007')
SELECT dbo.udf_GetNextWorkingDay ( '07/22/2007')

The result of all of the above test cases will be as following:
2007-07-23 00:00:00.000