duplicate removal cursor

Create procedure spBLSfixdupesproductvariant as

DECLARE @cnt int, @custID as int

DECLARE dupCursor CURSOR FAST_FORWARD

FOR SELECT PV.ProductID, COUNT(PV.ProductID) AS Cnt

    from aspdotnetstorefront8011.dbo.ProductVariant pv

join aspdotnetstorefront8011.dbo.product p

on pv.productid = p.productid

group by pv.productid, pv.createdon,cast(p.Extensiondata as varchar(40))

having count(pv.productid) > 1 and cast(p.Extensiondata as varchar(40))='BlueStar'

OPEN dupCursor

FETCH NEXT FROM dupCursor

INTO @custID, @cnt

WHILE @@FETCH_STATUS = 0

BEGIN

    DELETE Top (@cnt-1)

    FROM aspdotnetstorefront8011.dbo.ProductVariant

    WHERE ProductID= @custID

   

    FETCH NEXT FROM dupCursor

    INTO @custID, @cnt

END

CLOSE dupCursor

DEALLOCATE dupCursor

get database objects information for all databases on SQL Server

-- Part 1

Declare @sqlstr nvarchar(200) 

-- Part 2

/* drop the temporary table if already exists */

If Object_Id('tempdb..#tblDBObjects') is Not Null

Drop table #tblDBObjects

/* create temporary table */

Create TABLE #tblDBObjects (

dbName sysname,

objName varchar(200),

objtype char(2),

objtypename varchar(200),

crdate datetime,

)

-- Part 3

/*assign string value to variable */

Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, sysobjects.name, xtype,type_desc,crdate From ?..sysobjects join sys.objects on sysobjects.id=sys.objects.object_id'''

/* execute SQL string */

Exec sp_executesql @sqlstr

-- Part 4

/* select from temp table */

Select * From #tblDBObjects order by crdate desc

RETURN

Good SQL Comment Block to Use

/* ---------------------------------------------------------------------------- -- Object Name:  -- Project: -- Business Process:  -- Purpose: -- Detailed Description:   -- Database: -- Dependent Objects: -- Called By:  -- Upstream Systems: -- Downstream Systems: --  -------------------------------------------------------------------------------------- -- Rev | CMR | Date Modified | Developer | Change Summary -------------------------------------------------------------------------------------- --       |          |                      |               |  -- */

SQL Server Pedia Knowledge Base link

http://sqlserverpedia.com/wiki/Performance_Monitor

Microsoft Ramp Up link

http://msdn.microsoft.com/en-us/rampup/dd425032.aspx

Full text population TSQL 2005

This is a cursor based script from Microsoft to use for full text populations


-- Sample script to start full-text index population for
-- each full-text index.
create Procedure sp_STARTFULLTEXTPOPULATION as
DECLARE @table_name NVARCHAR(517),
@schema_name NVARCHAR(517);
DECLARE @exec_str NVARCHAR(4000);
DECLARE @change_tracking_state NCHAR(1);
-- Retrieve a list of tables with full-text indexes and start full
-- full-text index population for each full-text index.
DECLARE ms_crs_ftind CURSOR STATIC LOCAL FOR
SELECT t.name, SCHEMA_NAME(t.schema_id), ft.change_tracking_state
FROM sys.fulltext_indexes AS ft
JOIN sys.tables AS t
ON (ft.object_id = t.object_id);
OPEN ms_crs_ftind;
FETCH ms_crs_ftind INTO @table_name, @schema_name, @change_tracking_state;
WHILE @@FETCH_STATUS >= 0
BEGIN
-- Start full-text index population for each full-text index.
SELECT @exec_str = 'ALTER FULLTEXT INDEX ON '
+ QUOTENAME(@schema_name,'[')+'.'+QUOTENAME(@table_name,'[')
+ ' start full POPULATION ';
EXEC (@exec_str);
FETCH ms_crs_ftind INTO @table_name,@schema_name, @change_tracking_state;
END
DEALLOCATE ms_crs_ftind;


--For stop, just replace 'start full' with 'STOP'