473,892 Members | 1,424 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

A script to delete views

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

Sep 30 '06 #1
1 2144
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_SCH EMA.TABLES
WHERE
TABLE_TYPE = N'VIEW'
AND OBJECTPROPERTY(
OBJECT_ID(QUOTE NAME(TABLE_SCHE MA) +
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.co mwrote in message
news:11******** **************@ c28g2000cwb.goo glegroups.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

Oct 1 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2741
by: Alex Martelli | last post by:
All my mailboxes have been filling up with files of about 130k to 150k, no doubt copies of some immensely popular virus. So, I've no doubt lost lots of real mail because of "mailbox full" conditions (the proliferating fake bounce messages more or less ensure nobody knows their mail to me has bounced, either). As an emergency response I and Anna developed, over the last half hour, a small Python script to be run from cron every few...
0
1644
by: Mark Griffin | last post by:
Hi... I am trying to convert a simple FAQ script to access mysql db rather than the Access db it was designed for. The originators of the script offer no support at all. I have managed to set up the mysql db and have frigged the script to get it running, however certain bits are not functional, i.e. the links on the categories.
0
3157
by: Sjaak van Esdonk | last post by:
Hi all, i'm looking for a way to script all of my views in the correct order. Some views are dependant on other views and i want to be able to run the script in one time fully correct. Now when i create such a script i have to run it multiple times to generate all the scripts. p.s. i found a script here : http://www.eggheadcafe.com/articles/20030609.asp
16
17049
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums must be UPDATED, if not, they must be INSERTED. Logically then, I would like to SELECT * FROM <TABLE> WHERE ....<Values entered here>, and then IF FOUND UPDATE <TABLE> SET .... <Values entered here> ELSE INSERT INTO <TABLE> VALUES <Values...
3
2799
by: vcornjamb | last post by:
Hello, I am developing a web form that contains some buttons and a data grid which has as its last column link buttons that will delete the data associated with that row. Everything works fine, but users have requested a confirmation message pop up so the user can confirm the delete. I can not quite get this to work. Here are the facts: I am working in the Microsoft Development Environment 2003 (Version
1
1497
by: naren999 | last post by:
hi, i have lot of views which are registered to different hosts, in which many of hosts are not in the network. i am trying to remove all the views. the problem is some views are removed improperly, so i just want a script which can remove the views and which can unregister and removes the tag(for improperly removed views). can anyone help me out....
1
4665
by: Chris Zopers | last post by:
Hello, I want to generate a SQL Script of a SQL Server database. To do this, I right-click the database name and choose the option All Tasks/Generate SQL Script. Than I get a text file with a script that generates all objects, but the order in which the objects are generated is wrong. There are a lot of views in the database and many views are based on other views. When I try to execute the script on a different computer, I get a lot...
5
6707
by: Massimo | last post by:
The iussue: Sql 2K I have to keep in the database the data from the last 3 months. Every day I have to load 2 millions records in the database. So every day I have to export (in an other database as historical data container) and delete the 2 millions records inserted 3 month + one day ago. The main problem is that delete operation take a while...involving transaction log.
14
5425
by: asdf | last post by:
I have a python script whose output i want to dynamically display on a webpage which will be hosted using Apache. How do I do that? thanks
0
11241
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10836
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10926
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10468
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8018
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5857
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4683
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3288
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.