> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:
> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
sql
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Monday, March 26, 2012
Resetting DB
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
Resetting DB
Hi
Is there an easy way to drop all object of a db so it is as new? Just so I
can run a script to recreate the objects.
Thanks
RegardsHello,
In the Generate SQL Script, Script all objects with drop objects. While
executing the script on the database will drop and recreate all objects.
FYI, This will clear off all
the data, so if you need data please backup the database.
Thanks
Hari
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects.
>> Thanks
>> Regards
>>
>|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
>> Is there some reason why you can't add DROP DATABASE <database_name> and
>> CREATE DATABASE <database_name> statements to your script?
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects
>> Is there some reason why you can't add DROP DATABASE <database_name> and
>> CREATE DATABASE <database_name> statements to your script?
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "John" <John@.nospam.infovis.co.uk> wrote in message
>> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects.
>> Thanks
>> Regards
>>
>>
>|||Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:
> > Is there an easy way to drop all object of a db so it is as new? Just so I
> > can run a script to recreate the objects.
> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > Is there an easy way to drop all object of a db so it is as new? Just so I
> > can run a script to recreate the objects.
> >
> > Thanks
> >
> > Regards
> >
> >
>
Is there an easy way to drop all object of a db so it is as new? Just so I
can run a script to recreate the objects.
Thanks
RegardsHello,
In the Generate SQL Script, Script all objects with drop objects. While
executing the script on the database will drop and recreate all objects.
FYI, This will clear off all
the data, so if you need data please backup the database.
Thanks
Hari
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects.
>> Thanks
>> Regards
>>
>|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
>> Is there some reason why you can't add DROP DATABASE <database_name> and
>> CREATE DATABASE <database_name> statements to your script?
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects
>> Is there some reason why you can't add DROP DATABASE <database_name> and
>> CREATE DATABASE <database_name> statements to your script?
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "John" <John@.nospam.infovis.co.uk> wrote in message
>> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Is there an easy way to drop all object of a db so it is as new? Just so
>> I can run a script to recreate the objects.
>> Thanks
>> Regards
>>
>>
>|||Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:
> > Is there an easy way to drop all object of a db so it is as new? Just so I
> > can run a script to recreate the objects.
> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > Is there an easy way to drop all object of a db so it is as new? Just so I
> > can run a script to recreate the objects.
> >
> > Thanks
> >
> > Regards
> >
> >
>
Resetting DB
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:
> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:
> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
Resetting DB
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
Resetting DB
Hi
Is there an easy way to drop all object of a db so it is as new? Just so I
can run a script to recreate the objects.
Thanks
RegardsHello,
In the Generate SQL Script, Script all objects with drop objects. While
executing the script on the database will drop and recreate all objects.
FYI, This will clear off all
the data, so if you need data please backup the database.
Thanks
Hari
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>|||Just a thought here, but another way, if you're doing this often, might be t
o
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach a
s
needed - though that second option doesn't sound nearly as fast as just abou
t
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:
> Below is a script that will drop all objects. If the database is not larg
e
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>sql
Is there an easy way to drop all object of a db so it is as new? Just so I
can run a script to recreate the objects.
Thanks
RegardsHello,
In the Generate SQL Script, Script all objects with drop objects. While
executing the script on the database will drop and recreate all objects.
FYI, This will clear off all
the data, so if you need data please backup the database.
Thanks
Hari
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects
Is there some reason why you can't add DROP DATABASE <database_name> and
CREATE DATABASE <database_name> statements to your script?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>|||> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
I can't speak for John but I have had large SQL 2000 databases where I found
it considerably faster to drop all objects rather than dropping and
recreating the entire database. However, in the SQL 2005 world, the
database drop/create method is very fast thanks to instant file
initialization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Is there some reason why you can't add DROP DATABASE <database_name> and
> CREATE DATABASE <database_name> statements to your script?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>|||> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
Good point. I wasn't thinking along those lines. Thanks for including this
information.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:74A9F97B-3095-471B-A3C7-BF6EE085B1D6@.microsoft.com...
> I can't speak for John but I have had large SQL 2000 databases where I
> found it considerably faster to drop all objects rather than dropping and
> recreating the entire database. However, in the SQL 2005 world, the
> database drop/create method is very fast thanks to instant file
> initialization.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%233GfsyheHHA.3960@.TK2MSFTNGP02.phx.gbl...
>|||Just a thought here, but another way, if you're doing this often, might be t
o
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach a
s
needed - though that second option doesn't sound nearly as fast as just abou
t
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:
> Below is a script that will drop all objects. If the database is not larg
e
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>sql
Reseting Identity Column using SQL
Hi, Can anyone write me a script, how can I reset identity on column? ( I want to start records from 1 again)
Thanks, radco
Take a look at the following pointer:http://msdn2.microsoft.com/en-US/library/ms176057.aspxFriday, March 23, 2012
Reset permissions
Hello,
I am looking for a script that will work dynamically depending on which
database I am in. I want to run the GRANT or DENY based upon a certain group
and I would prefer not to select each table the EM. Is there a script out
there that can read the tables and generate a permission script off of it?
Thanks in advance.
Jakesp_msForeachtable 'Grant select on ? to Public'
Change What u want on the Statement but ?
"jake" <rondican@.hotmail.com> wrote in message
news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am looking for a script that will work dynamically depending on
which
> database I am in. I want to run the GRANT or DENY based upon a certain
group
> and I would prefer not to select each table the EM. Is there a script out
> there that can read the tables and generate a permission script off of it?
> Thanks in advance.
> Jake
>|||Hi,
I do not recommend 'sp_msForeachtable' because it is undocumented. I
strongly urge all of you to think long and hard before embedding calls to
undocumented APIs in production code. Some of these undocumented APIs are
gone in Yukon.
I recommend building some simple code generators like the one shown below.
FYI, in Yukon you can GRANT/DENY/REVOKE permissions at different scopes. So
you can do this:
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in schema
--
GRANT EXECUTE ON SCHEMA :: someschema TO someuser
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in all schemas in the current database
--
GRANT EXECUTE TO someuser
Regards,
Clifford Dibble
Program Manager
SQL Server Engine
create function make_sql(@.sqltemplate nvarchar(2000)
, @.gdr nvarchar(6)
, @.perm nvarchar(128)
, @.object nvarchar(128)
, @.user nvarchar(128)
)
returns nvarchar(4000) as
begin
declare @.sql nvarchar(4000)
select @.sql = replace(replace(replace(replace(@.sqltemp
late, '<gdr>',
@.gdr), '<perm>', @.perm), '<object>', @.object), '<user>', @.user)
return @.sql
end
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'GRANT', 'SELECT',
o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'REVOKE',
'INSERT', o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
"Melih SARICA" wrote:
> sp_msForeachtable 'Grant select on ? to Public'
> Change What u want on the Statement but ?
> "jake" <rondican@.hotmail.com> wrote in message
> news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> which
> group
>sql
I am looking for a script that will work dynamically depending on which
database I am in. I want to run the GRANT or DENY based upon a certain group
and I would prefer not to select each table the EM. Is there a script out
there that can read the tables and generate a permission script off of it?
Thanks in advance.
Jakesp_msForeachtable 'Grant select on ? to Public'
Change What u want on the Statement but ?
"jake" <rondican@.hotmail.com> wrote in message
news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am looking for a script that will work dynamically depending on
which
> database I am in. I want to run the GRANT or DENY based upon a certain
group
> and I would prefer not to select each table the EM. Is there a script out
> there that can read the tables and generate a permission script off of it?
> Thanks in advance.
> Jake
>|||Hi,
I do not recommend 'sp_msForeachtable' because it is undocumented. I
strongly urge all of you to think long and hard before embedding calls to
undocumented APIs in production code. Some of these undocumented APIs are
gone in Yukon.
I recommend building some simple code generators like the one shown below.
FYI, in Yukon you can GRANT/DENY/REVOKE permissions at different scopes. So
you can do this:
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in schema
--
GRANT EXECUTE ON SCHEMA :: someschema TO someuser
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in all schemas in the current database
--
GRANT EXECUTE TO someuser
Regards,
Clifford Dibble
Program Manager
SQL Server Engine
create function make_sql(@.sqltemplate nvarchar(2000)
, @.gdr nvarchar(6)
, @.perm nvarchar(128)
, @.object nvarchar(128)
, @.user nvarchar(128)
)
returns nvarchar(4000) as
begin
declare @.sql nvarchar(4000)
select @.sql = replace(replace(replace(replace(@.sqltemp
late, '<gdr>',
@.gdr), '<perm>', @.perm), '<object>', @.object), '<user>', @.user)
return @.sql
end
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'GRANT', 'SELECT',
o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'REVOKE',
'INSERT', o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
"Melih SARICA" wrote:
> sp_msForeachtable 'Grant select on ? to Public'
> Change What u want on the Statement but ?
> "jake" <rondican@.hotmail.com> wrote in message
> news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> which
> group
>sql
Tuesday, March 20, 2012
reregister sqlDmo.dll
When I try to script my database, I get the following error:
schema_name is not recognized function name
I found that I have to reregister sqlDmo.dll
How can I do that?
Thank you,
Simonregsvr32.exe "%ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll"
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:OQHzo5pCFHA.4004@.tk2msftngp13.phx.gbl...
> When I try to script my database, I get the following error:
> schema_name is not recognized function name
> I found that I have to reregister sqlDmo.dll
> How can I do that?
> Thank you,
> Simon
>
schema_name is not recognized function name
I found that I have to reregister sqlDmo.dll
How can I do that?
Thank you,
Simonregsvr32.exe "%ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll"
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:OQHzo5pCFHA.4004@.tk2msftngp13.phx.gbl...
> When I try to script my database, I get the following error:
> schema_name is not recognized function name
> I found that I have to reregister sqlDmo.dll
> How can I do that?
> Thank you,
> Simon
>
Subscribe to:
Posts (Atom)