Thursday, June 7, 2012

How to delete procedures, user functions or views from a database in SQL?

In order to delete all functions, procedures or views from a database you can help yourself by using this query:

DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.procedures WHERE type in ('p', 'fn') and is_ms_shipped=0 and name not like 'sp[_]%diagram%'

      OPEN cur

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

How to get list with procedures, functions or views in Sql?

In order to obtain a list with procedures(P), functions(FN) or views(V) in Sql you can run this query:

SELECT [name], [type], create_date, modify_date
FROM sys.objects
WHERE [type] in ('P', 'FN', 'V')
order by [Type ] asc