Use of Sys Tables and If Exists condition in SQL Server

In SQL server, several times we need to check that if object is already exists than first drop that object to run a script so for this we can use below query:

IF Object_id(N'tempdb.[dbo].[#Sales_Data]', N'U') IS NOT NULL

  BEGIN

      DROP TABLE [dbo].[#Sales_Data]

  END

Below query provides a way to check a condition for insert or update records in a table:

IF EXISTS (SELECT * FROM TableName WHERE Column1='Value')

    UPDATE TableName SET (...) WHERE Column1='Value'

ELSE

    INSERT INTO TableName VALUES (...)

To search a column with their table’s name:

SELECT c.name as ColumnName, t.name as TableName from sys.tables t

inner JOIN sys.columns c

ON t.object_id=c.object_id

WHERE c.name LIKE '%columnName%'

To list all tables from each database of your database server:

 sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

To get list of all user tables

select name TableName from sysobjects where type='U'

 

EXEC sp_tables @table_name="%", @table_owner="%",@table_type="'TABLE'"