I have been wanting to write some posts lately of some productivity tools and tricks. My last post was for a VS-Addin for updating the reference paths for all "selected" projects in your visual solution set.
This post is for a sql script that enables you to drop a series of stored procedures, UDF's or Views. I thought about adding tables but then I would have to handle keys/constraints as well....maybe next time.
PRINT N'Dropping procedures/functions...'
GO
PRINT N''
GO
declare @name varchar(256), @type varchar(2), @sql nvarchar(1000), @printText varchar(1000)
-- declare cursor
declare localCursor cursor local fast_forward read_only for
select [name], [type]
from [sysobjects]
where [type] in ('P','TF','FN','V')
and (
[name] in
(
'[Put procedure/view or udf name here]',
'[Put another procedure/view or udf name here]'
)
)
order by [type],[name]
-- now open the cursor
open localCursor
fetch next from localCursor into @name, @type
-- now loop through building list.
while @@fetch_status=0
begin
if( @type = 'P' )
begin
set @printText = 'Dropping procedure ' + @name + ' ...'
set @sql = 'drop procedure ' + @name
end
else if ( @type in ('TF','FN') )
begin
set @printText = 'Dropping function ' + @name + ' ...'
set @sql = 'drop function ' + @name
end
else if ( @type in ('V') )
begin
set @printText = 'Dropping view ' + @name + ' ...'
set @sql = 'drop view ' + @name
end
print @printText
exec sp_executesql @sql
fetch next from localCursor into @name, @type
end
close localCursor
deallocate localCursor
HTH,
-Mathew Nolton
Update Project References Add-In
Visual Studio doesn't natively support the ability to update all selected project's within a Solution with the same reference paths. This is important (at least to me) if you have to download from your source control system a different version of your source code. Since you typically do not check in your project preferences into source control, you will be stuck with the mundane task of selecting your project, setting the reference path, select the next project, etc.
This add-in enables you to select a given set of projects within your solution, set your reference paths and make updates across all selected projects. For example:
You have a couple of options
- Completely Replace Existing References...enough said
- Do nothing if a reference exists (and append to end of list if it doesn't)
- Prepend to List...(note: if the reference already exists, it will be moved).
- Append to List...(note: if the reference already exists, it will be moved).
To use the add-in, copy the files UpdateReferencePaths.AddIn and UpdateReferencePaths.dll to your add-in directory. Typically, this is located at: C:\Documents and Settings\[Your User Name Here]\My Documents\Visual Studio 2005\Addins
Get the Add-In and the source code here: http://www.cybral.com/tools.aspx#updatereferencepaths
-Mathew Nolton
http://www.cybral.com