Drop Everything in SQL Server 2005 Database

/* Drop all non-system stored procs */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null

BEGIN

SELECT @SQL = 'DROP PROCEDURE [' + RTRIM(@name) +']'

EXEC (@SQL)

PRINT 'Dropped Procedure: ' + @name

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

/* Drop all views */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP VIEW [' + RTRIM(@name) +']'

EXEC (@SQL)

PRINT 'Dropped View: ' + @name

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

/* Drop all functions */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP FUNCTION [' + RTRIM(@name) +']'

EXEC (@SQL)

PRINT 'Dropped Function: ' + @name

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

/* Drop all Foreign Key constraints */

DECLARE @name VARCHAR(128)

DECLARE @constraint VARCHAR(254)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null

BEGIN

SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

WHILE @constraint IS NOT NULL

BEGIN

SELECT @SQL = 'ALTER TABLE [' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)

EXEC (@SQL)

PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name

SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

END

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

END

GO

/* Drop all Primary Key constraints */

DECLARE @name VARCHAR(128)

DECLARE @constraint VARCHAR(254)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL

BEGIN

SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

WHILE @constraint is not null

BEGIN

SELECT @SQL = 'ALTER TABLE [' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)

EXEC (@SQL)

PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name

SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

END

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

END

GO

/* Drop all tables */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP TABLE [' + RTRIM(@name) +']'

EXEC (@SQL)

PRINT 'Dropped Table: ' + @name

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

SQL to Select a random row from a database table

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

To Generate Table Structure in Excel Sheet.

This blog help the people who want to generate a excel sheet of the all table structure for specified database. Following is the procedure which generate a Excel sheet in your D drive for all table structure of specified database.

Before Executing this procedure you have to follow following settings.

1).Microsoft SQL Server 2005 -> Configuration Tools -> Surface Area Configuration -> Surface Area Configuration for Features

2) Mark True on Enable Web Assistant.

Or

Run Following query in sql server which will enable web assistant procedures.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Web Assistant Procedures', 1;

GO

RECONFIGURE

GO

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SP_CREATE_EXCEL_STRUCTURE_FOR_ALLDATABASE]

(

@db_name VARCHAR(100)

)

AS

BEGIN

DECLARE @columns VARCHAR(8000), @sql VARCHAR(8000), @data_file VARCHAR(100)

DECLARE @file_name varchar(100),@table_name VARCHAR(100)

DECLARE @Constraint VARCHAR(50)

DECLARE C1 CURSOR FOR select name from sys.tables

OPEN C1

FETCH NEXT FROM C1 INTO @table_name

WHILE @@FETCH_STATUS=0

BEGIN

drop TABLE MYTMP

SELECT @Constraint=B.COLUMN_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A,

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND A.TABLE_NAME=@table_name

SELECT column_name FieldName,Data_type DataType,

character_maximum_length Size,is_nullable AllowNulls

INTO MYTMP FROM information_schema.columns

WHERE table_name=@table_name

ALTER TABLE MYTMP ADD PrimaryKey VARCHAR(20)

ALTER TABLE MYTMP ADD ForeignKey VARCHAR(70)

ALTER TABLE MYTMP ADD AutoIncrement VARCHAR(20)

ALTER TABLE MYTMP ADD DefaultValue VARCHAR(50)

ALTER TABLE MYTMP ADD Description VARCHAR(500)

UPDATE MYTMP SET PrimaryKey='YES' WHERE FieldName=@Constraint

UPDATE MYTMP SET PrimaryKey='' WHERE FieldName<>@Constraint

---=================To Add Foreign key=================================

UPDATE MYTMP SET ForeignKey=Tmp.ForeignKey

From

(SELECT COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id)

AS ReferenceColumnName,

OBJECT_NAME (f.referenced_object_id)+

'('+COL_NAME(fc.referenced_object_id,fc.referenced_column_id)+')'

As ForeignKey

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc ON

f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME(f.parent_object_id)=@TABLE_NAME)As Tmp

WHERE FieldName=Tmp.ColumnName

UPDATE MYTMP SET ForeignKey='' WHERE ForeignKey IS NULL

----======================To Set Default Value===============================

UPDATE MYTMP SET DefaultValue=Tmp.TEXT

FROM

(SELECT name,REPLACE(REPLACE(TEXT,'(',''),')','')TEXT FROM syscolumns

inner join syscomments on(syscomments.id=syscolumns.cdefault)

WHERE syscolumns.id = object_id(@Table_name) AND cdefault > 0)As Tmp

WHERE FieldName=Tmp.Name

UPDATE MYTMP SET DefaultValue='' WHERE DefaultValue IS NULL

----======================= To set Auto Increment============================

UPDATE MYTMP SET AutoIncrement='YES'

FROM

(SELECT NAME AS COLUMNNAME FROM SYS.IDENTITY_COLUMNS

WHERE OBJECT_NAME(OBJECT_ID)=@Table_name)AS TMP

WHERE FieldName=Tmp.ColumnName

UPDATE MYTMP SET AutoIncrement='' WHERE AutoIncrement IS NULL

----========================To Set Description ==============================

UPDATE MYTMP SET Description=Tmp.Description

FROM

(SELECT convert(varchar(500),ep.value) AS Description,c.name AS ColumnName

FROM sys.objects o

INNER JOIN sys.extended_properties ep ON o.object_id = ep.major_id

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

LEFT JOIN syscolumns c ON ep.minor_id = c.colid AND ep.major_id = c.id

WHERE o.type='U' and o.name=@table_name)As Tmp

Where FieldName=Tmp.ColumnName

UPDATE MYTMP SET Description='' WHERE Description IS NULL

---===============================================================

SET @file_name='D:\'+@table_name+'.xls'

--===================================

exec sys.sp_makewebtask @outputfile = @file_name,

@query = 'Select * from mytmp',

@colheaders =1,

@FixedFont=0,@lastupdated=1,@resultstitle=@table_name,

@dbname =@db_name

--========================================

FETCH NEXT FROM C1 INTO @table_name

END

CLOSE C1

DEALLOCATE C1

END

------------------TO EXECUTE

--SP_CREATE_EXCEL_STRUCTURE_FOR_ALLDATABASE 'jito'