May 20, 2017 at 8:57 pm
I'm using SQL Server 2008 and its Management Studio.
I'd like to delete all user defined stored procedure & views.
Is there any script to execute ?
May 21, 2017 at 7:53 am
spectra - Saturday, May 20, 2017 8:57 PMI'm using SQL Server 2008 and its Management Studio.
I'd like to delete all user defined stored procedure & views.Is there any script to execute ?
This is very similar to your previous question about tables. The mechanism to build the statements is the same, as is the need to execute the statements returned by the query.
This query will generate the statements to drop your stored procedures:
SELECT 'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.objects
WHERE type = 'P'
ORDER BY name;
This will generate the statements to drop your views:
SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.objects
WHERE type = 'V'
ORDER BY name;
May 21, 2017 at 11:14 am
that is very much helpful.
thanks
May 21, 2017 at 8:27 pm
spectra - Sunday, May 21, 2017 11:14 AMthat is very much helpful.
thanks
Glad it worked for you. Thanks for the feedback.
May 23, 2017 at 1:59 am
Also you can open
1. Object Explorer Details
2.Navigate to Stored Procedures Folder under respective database in Object Explorer--> This will show you all procedure in Object Explorer Details
3. Select all or required procedures from there and then right click Delete.
May 23, 2017 at 5:52 am
Ed Wagner - Sunday, May 21, 2017 7:53 AMspectra - Saturday, May 20, 2017 8:57 PMI'm using SQL Server 2008 and its Management Studio.
I'd like to delete all user defined stored procedure & views.Is there any script to execute ?
This is very similar to your previous question about tables. The mechanism to build the statements is the same, as is the need to execute the statements returned by the query.
This query will generate the statements to drop your stored procedures:
SELECT 'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.objects
WHERE type = 'P'
ORDER BY name;This will generate the statements to drop your views:
SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.objects
WHERE type = 'V'
ORDER BY name;
You could also use the specific system views.
SELECT 'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.procedures
ORDER BY name;
SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.views
ORDER BY name;
May 23, 2017 at 7:51 am
Luis Cazares - Tuesday, May 23, 2017 5:52 AMYou could also use the specific system views.
SELECT 'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.procedures
ORDER BY name;SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.views
ORDER BY name;
They'll definitely work. I generally use sys.objects for almost everything.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply