SQL SERVER TIPS & TRICKS

Important SQL SERVER Queries:

1.Using the Information Schema

             • SQL Query to find a particular table into the entire database.

SELECT name FROM sys.databases WHERE  CASE
WHEN state_desc = 'ONLINE'
THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[customers]', 'U')
END IS NOT NULL

            • SQL Query to delete all tables of the Active database

DECLARE @sql NVARCHAR(max)=''
SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'
+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql 

• SQL Query to drop all views from the active table.

DECLARE @sql  VARCHAR(max) = '',
        @crlf VARCHAR(2) = Char(13) + Char(10);

SELECT @sql = @sql + 'DROP VIEW '
              + Quotename(Schema_name(schema_id)) + '.'
              + Quotename(v.NAME) + ';' + @crlf
FROM   sys.views v

PRINT @sql;

EXEC(@sql);  
 

For Further Reading, Please Visit the below link

My Publications