IMG-LOGO

How to delete all Views Table in TSQL?

andy - 22 Dec, 2020 1118 Views 0 Comment

Need to delete all the user creation views in TSQL? You can easily delete the views using the while loop method against the sysobjects table.

You may wonder what is a view table is? A view table can be considered as a virtual table that runs a query and will return a row of records. Sometimes you need to give a set of data to some users. You can create a view with the data that you only want to give access. Also, you do need to create a new table, we can manipulate the data by performing a new query against the existing table which will save us some data storage as well.

Here is the full snippet TSQL query to delete the user creation view in TSQL.


DECLARE @spName VARCHAR(128)
DECLARE @SQL VARCHAR(500)

SELECT @spName = (
	SELECT TOP 1 [name] 
	FROM sysobjects 
	WHERE [type] = 'P' 
	AND category = 0 
	ORDER BY [name]
)

WHILE @spName is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@spName) +']'
    EXEC (@SQL)
    PRINT 'Deleted Stored Procedure: ' + @spName
    SELECT @spName = ((
		SELECT TOP 1 [name] 
		FROM sysobjects 
		WHERE [type] = 'P' 
		AND category = 0 
		ORDER BY [name]
	)
END
GO

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.

Related Articles

How to restore database using SQL query in TSQL?

If you have a database backup bak file extension and want to restore it using SQL query You can use the built in RESTORE DATABASE function Remember in order to be able to restore a database successfully You need to ...

How to get all table sizes in TSQL?

To get the information about how much space or size used by tables You can retrieve the size information by linking multiple tables in sys tables There are two tables that hold this information The first one is the sys ...