SQLServer function to update the same table where cursor selects values

--Check whether cursor is open

IF CURSOR_STATUS('global','c4') >= 0

BEGIN

        CLOSE c4

        DEALLOCATE c4

END

DECLARE @comparison VARCHAR(200)

DECLARE @source VARCHAR(200)

DECLARE @supplier VARCHAR(200)

DECLARE @itemname VARCHAR(200)

DECLARE @atc3names VARCHAR(max)

DECLARE c4 CURSOR FOR

SELECT DISTINCT

        [Comparison NAME],     

        [Data Supplier NAME],  

        [Data Source NAME],    

        [Item NAME]

FROM dbo.[Data Validation Attribute Items] WHERE [Comparison NAME] = 'Product'

FOR UPDATE OF [ATC3]

OPEN c4

FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname

WHILE @@FETCH_STATUS = 0

BEGIN

        -- Combine multile rows in to a column seperated by a ';'

        SET @atc3names  = 'DECLARE @atc3names  AS VARCHAR(200)

        SELECT @atc3names = COALESCE( @atc3names + '';'', '''') + [ATC3 Name]

        FROM

        (

        SELECT DISTINCT [ATC3 Name]

        FROM dbo.[' + @source + ' Fact Data] a

                INNER JOIN dbo.[Product Dim Product-Pack-Country] b

ON a.[Product-Pack-Country KEY] = b.[Product-Pack-Country KEY]

                INNER JOIN dbo.[Product Dim Product-Country] c

ON b.[Product-Country KEY] = c.[Product-Country KEY]

                INNER JOIN dbo.[Product Dim Product-Pack] f 

                        ON b.[Product-Pack KEY] = f.[Product-Pack KEY]

                INNER JOIN dbo.[Product] e ON c.[Product KEY] = e.[Product KEY]

                INNER JOIN dbo.[ATC4] g ON f.[ATC4 KEY] = g.[ATC4 KEY]

                INNER JOIN dbo.[PRODUCT DIM ATC4] h ON h.[ATC4 KEY] = g.[ATC4 KEY]

                INNER JOIN dbo.[ATC3] i ON i.[ATC3 KEY] = h.[ATC3 KEY]

        WHERE e.[Product NAME] = ''' + @itemname + '''

        ) A

       

        UPDATE .dbo.[Data Validation Attribute Items]

        SET [ATC3] = @atc3names

        WHERE [Comparison NAME] = ''Product''

                AND [Data Source NAME] = ''' + @source + '''

                AND [Item NAME] = ''' + @itemname +''''

        EXEC (@atc3names)

        SET @atc3names  = '';

FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname

END

CLOSE c4

DEALLOCATE c4

GO

Find an object in a SQL Server Instance (across databases)

Following block generates a concatenated string for a set of selected databases defined in the sub query.
DECLARE @command VARCHAR(max) ;
SELECT @command = coalesce (@command + 'union' , ' ') +
'    SELECT ''' + [DBName] + '''AS DBName,
    [type_desc] AS ObjectType
    FROM ['+ [DBName] + '].sys.objects
    WHERE [name] = ''ATC4''
'
FROM (SELECT TOP 1000 name AS DBName FROM master.sys.sysdatabases) d
EXEC (@command)

sp_MSforeachdb is an undocumented stored procedure, which can be used to run sql statements across databases in a SQL Server instance.
DECLARE @command varchar(1000)
SELECT @command =
'    USE [?] ; SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM sys.objects
    WHERE name = ''ATC4''
'
EXEC sp_MSforeachdb @command ;

Following cursor can also be used to execute SQL Statements across databases in a SQL Server instance.
DECLARE @DB_name varchar(1000)
DECLARE cur CURSOR LOCAL for
    SELECT name FROM master.sys.databases
OPEN cur
FETCH NEXT FROM cur into @DB_name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM [' + @DB_name + '].sys.objects
    where name = ''ATC4''
    ')
    FETCH NEXT FROM cur INTO @DB_name
END
CLOSE cur
DEALLOCATE cur

Generate date range (Year and Month)

Following  T-SQL query can be used to generate Month Names and Year Names within a given period of time.

DECLARE
    @StartDate DATETIME,
    @EndDate   DATETIME;
--Define start date and end date
SELECT @StartDate = '2013-04-01', @EndDate = '2014-03-01';
WITH cteDates AS (
    SELECT TOP (
        DATEDIFF (mm, @StartDate, @EndDate) + 1)
        DATEADD( mm, DATEDIFF(mm,0, @StartDate ) + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0
     ) AS MonthDate
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT
    YEAR(MonthDate) AS [Year],
    MONTH(MonthDate) AS [Month]
FROM cteDates

Ref:http://stackoverflow.com/questions/4181286/t-sql-select-get-all-months-within-a-range-of-years

View meta data - system objects - SQLServer

View list of constraints for a specific table
Select table_catalog, table_name, constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where table_name = 'area' order by table_name asc

View constraint names and table name for a specific column within the DB
Select table_catalog, table_name, column_name, constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where column_name = 'product-pack key' order by table_name asc

Remove Duplicate Records

Following set of codes remove duplicate records from a SQL Server table, based on one or more unique fields.

WITH NewDataSet AS
( SELECT s.*, DENSE_RANK() OVER (PARTITION BY s.productid order by newID()) as r
FROM isuradb.dbo.Products s
)
SELECT r FROM NewDataSet
DELETE FROM NewDataSet WHERE r <> 1

DENSE_RANK() function returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.