Drop All Functions in SQL SERVER

Share |

To DROP All function in SQL SERVER Database use following lines of codes, use it in Query window for particular database.

This code will save lots of time as DROPing All functions in SQL Server database is not a manual process, if we use this

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 

WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') 

AND category = 0 ORDER BY [name]) 

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND 
category = 0 AND [name] > @name ORDER BY [name])
END
GO

Read more...

Drop All Views in SQL SERVER

Share |

This is a very common scenario while updating or replacing database in SQL SERVER, we might need to delete all the views in SQL SERVER database. But to delete one view at a times is a time consuming process if database is huge with lots of Views.

To Drop all views in SQL SERVER database use following

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
WHERE [type] = 'V' AND category = 0 AND [name] > @name 
ORDER BY [name])
END
GO

Read more...

Delete All Store Procedures in SQL SERVER

Share |

While working on SQL Server 2008, there was a situation when I had to delete one Store Procedure at a time :(.
After doing some research to delete all store procedures with some query, I succeeded to save 2-3 hours or labour work :P

To delete all store procedures in single we can use following code in SQL Query.

DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type = 'p'
      OPEN cur

      FETCH NEXT FROM cur INTO @procedureName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @procedureName)
            FETCH NEXT FROM cur INTO @procedureName
      END
      CLOSE cur
      DEALLOCATE cur

This will surely save time, required to Delete All Store Procedures in SQL SERVER Database

Read more...