WISE SCRIPTS - DATABASE EMPTY

Check if database is empty before applying your scripted stored procs, tables, triggers, etc.

Rem ***** Start - SQL Server check if database is empty

Rem ***** http://www.mcdbadirectory.com - by mark wehmhoefer 10/26/2000

Rem ***** WISE script for finding if database is empty. Use ful when

creating

Rem ***** a new database in a preallocated but empty database

Rem *****Build the commands to get the SQL version

Delete File(s) %TEMPINFILE%

Insert Line "set nocount on" into Text File %TEMPINFILE%

Rem ***** See other snippet for setting the value of variable SQLVERSION

Rem ***** In SQL 7.0 - schema objects exist even in an empty database

If SQLVERSION contains "7.00" then

Insert Line "if exists (select 1 from sysobjects where type not in ('S',

'V'))" into Text File %TEMPINFILE%

else

Insert Line "if exists (select 1 from sysobjects where type <> 'S')" into

Text File %TEMPINFILE%

end

Insert Line " raiserror ('Database is not empty - user objects

detected',18,1)" into Text File %TEMPINFILE%

Insert Line "go" into Text File %TEMPINFILE%

Rem *****Use ISQL with the above tempinfile commands and output to

tempoutfile

Rem ***** %ISQLSWITCHES could be thee values:

Rem ***** trusted connection = -Syourserver -n -w300 -E

Rem ***** sql login connection = -Syourserver -n -w300 -E -Usa -Pyourpsw

Execute isql %ISQLSWITCHES% -h-1 -i%TEMPINFILE% -o%TEMPOUTFILE%

Rem *****Loop through the ISQL output text file tempoutfile for any Msg

(error occurred)

Set Variable SRCHSTR to Database

Read Lines of File %TEMPOUTFILE% into variable LINEIN start block

If expression true Left$(LINEIN,8)=SRCHSTR then

display message "Database is not empty - user objects detected"

exit installation

End

End Block

Rem ***** End - SQL Server check if database is empty