Adding Identity field dynamically to a sql table

To add an identity field when a table has no identity field use

Select Row_Number() over (order by [any Column name]) as Id,[Col1],col2,..... from [Table Name]

Find and Clear Duplicates from sql table

To Find duplicates:

select [col1],[col2] from [TableName] where [condition]
group by [col1],[col2],[col3],[col4] having count(*) > 1


Example:

select CompanyName, State from registerinfo where RegisterinfoId >12000
group by Companyname, State, Add1, City having count(*) > 1


To Clear Duplicates:

SELECT *
FROM registerinfo
WHERE registerinfoid IN
(
SELECT registerinfoid
FROM registerinfo
WHERE EXISTS
(
SELECT NULL
FROM registerinfo AS tmptable
WHERE registerinfo.Companyname = tmptable.companyname and
registerinfo.add1 = tmptable.add1 and
registerinfo.State = tmptable.state
HAVING
registerinfo.registerinfoid > MIN(tmptable.registerinfoid)
)
)

Drop all the tables from a database

To delete all the tables from a database:

Select 'drop table ' + Object_name(object_id) from sys.objects where type = 'U'

(or)

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"



To Delete All Records from a table:

Select 'delete from ' + Object_name(object_id) from sys.objects where type = 'U'