DZone Snippets: SQL Server code

SQL SERVER: Delete Duplicate Rows with Primary Id

cornerblue (CornerBLUE, Inc.) — Fri, 31 Aug 2007 19:12:09 GMT

Deletes duplicates (leaving one instance) where the table has a primary key. Good for tables with Id, DupColumn, DupColumn...

(This is MS-SQL specific)



DELETE

FROM 	TableName

WHERE 	Id NOT IN

	(SELECT 	MAX(Id)

        FROM   		TableName

        GROUP BY 	DuplicateColumName1, DuplicateColumName2)

Search for specific text in all stored procedures

mrtrombone (Mark Easton) — Thu, 26 Apr 2007 03:08:05 GMT



declare @search varchar(50)

SET @search = 'searchterm'



SELECT    

     ROUTINE_NAME,

     ROUTINE_DEFINITION

FROM    

    INFORMATION_SCHEMA.ROUTINES

WHERE    

    ROUTINE_DEFINITION LIKE @search

ORDER BY

    ROUTINE_NAME

Query and join tables across server instances.

gmiller (Greg Miller) — Sun, 31 Dec 2006 09:53:17 GMT

Use the following to query or join tables on remote servers. The query must be executed on a system running SQL Server, but the remote system can be pretty much anything an ODBC driver exists for:



select * from OpenDataSource(’SQLOLEDB‘,’Data Source=server.asdf.com;User ID=yourusername;Password=yourpassword‘).somedatabase.dbo.sometable;