|
|
Posted by Dan Guzman on 10/01/06 01:10
> 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
<namewitheldbyrequest@gmail.com> wrote in message
news:1159647523.344352.188610@c28g2000cwb.googlegroups.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
>
Navigation:
[Reply to this message]
|