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...

Delete All Tables MSSQL

Share |

While working on ASP.Net application, I came across a situation where I had to delete all the Tables of the MSSQL 2008 database, and it was really painful process. 

In this case I had to delete all the SPs and all views as well. As I started searching like "Truncate database Sql 2008" or "Delete all tables in MSSQL" I came across two things and it worked for me.

(I am really not sure whether it works in all the cases but for Deleting all tables in SQL, this worked for me.) 

Firstly we need to remove all indexs from SQL table, I found following code when I Google the term 

DECLARE @indexName NVARCHAR(128)
DECLARE @dropIndexSql NVARCHAR(4000)

DECLARE tableIndexes CURSOR FOR
SELECT name FROM sysindexes
WHERE id = OBJECT_ID(N'tableName') AND
  indid > 0 AND indid < 255 AND
  INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY indid DESC

OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName
WHILE @@fetch_status = 0
BEGIN
  SET @dropIndexSql = N'DROP INDEX tableName.' + @indexName
  EXEC sp_executesql @dropIndexSql

  FETCH NEXT FROM tableIndexes INTO @indexName
END

CLOSE tableIndexes
DEALLOCATE tableIndexes

After this we need to delete all the tables in SQL, for this I used following SP

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

It seems that this is a HIDDEN stored procedure in MSSQL.

Read more...