SQL : Drop Table with a predifined keyword

To drop all the tables starting with a certain string .

        declare @cmd varchar(4000)

declare cmds cursor for

Select

    'drop table [' + Table_Name + ']'

From

    INFORMATION_SCHEMA.TABLES

Where

    Table_Name like 'prefix%'

open cmds

while 1=1

begin

    fetch cmds into @cmd

    if @@fetch_status != 0 break

    exec(@cmd)

end

close cmds

deallocate cmds

SQL : Check for duplicate table name

Here’s how I checked for duplicate names on one table:

SELECT columnname,

 COUNT(columnname) AS NumOccurrences

FROM tablename

GROUP BY columnname

HAVING ( COUNT(columnname) > 1 )

Delete Duplicate Records using SQL

Here’s an helpful links to delete duplicate records.

http://www.cryer.co.uk/brian/sql/sql_delete_duplicates.htm

Tip: How to change the schema of tables in SQL

ALTER SCHEMA dbo TRANSFER dbuser.table1

This will transfer the ownership of table1 from dbuser schema to dbo.

SQL Tip: Resetting Identity seed

After deleting your db table, if you want your identity column to start from 1 again

DBCC CHECKIDENT('TableName', RESEED, 0)

SQL views

In my own words, sql views is a virtual table created by getting partial (or even full) data from a table or combination of tables.

Normal sql queries can be used to retrieve data from Views like any other table.