I need a script that I can run from ASP .Net that will delete all
views that start with "Search". My site creates them on the fly and
they tend to accumulate as more users visit the site. Is there a good
SQL help web site that I can refer to that will be me started?
The script below will delete all dbo-owned views that begin with 'Search'.
However, creating/deleting objects from normal application code is not
secure and often an indication of an application design flaw.
SET NOCOUNT ON
DECLARE @DropStatement nvarchar(4000)
DECLARE @LastError int
DECLARE DropStatements
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
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)),
'IsMSShipped') = 0
AND TABLE_SCHEMA = N'dbo'
AND TABLE_NAME LIKE N'Search%'
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @DropStatement
IF @@FETCH_STATUS = -1 BREAK
BEGIN
EXECUTE sp_ExecuteSQL @DropStatement
SET @LastError = @@ERROR
IF @LastError 0
BEGIN
BREAK
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
--
Hope this helps.
Dan Guzman
SQL Server MVP
<na******************@gmail.comwrote in message
news:11**********************@c28g2000cwb.googlegr oups.com...
Hi,
I need a script that I can run from ASP .Net that will delete all
views that start with "Search". My site creates them on the fly and
they tend to accumulate as more users visit the site. Is there a good
SQL help web site that I can refer to that will be me started?
Thanks,
Bill
Cincinnati, OH USA
