How to check views tables SP exists in sql server or not ?

Some times in the sql we need to run many scripts at once. Some developers will select particular (create,alter, drop queries one by one) script and run it. This is not a good idea .So while developing the DB script the developer need to maintain the script file as a single file and the single file must check the the schema's already exist or not, if exist no need to run some time. Some time you need to run the script to alter previous one. For that the below functionality will help you to run script easily at one time effort. It will mostly help you while deploying the

How to check view exist in sql server or not?

In the SQL there are several methods to check the views are available or not. The below code is one of the easiest way to check the views information.

First we need to check the views are exist or not. If exist means drop the view and recreate the new one.

Query:

if exists(select count(*) from INFORMATION_SCHEMA.VIEWS where table_name = 'vwMyview' )

BEGIN

drop view vwMyview

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[ vwMyview]

AS

SELECT dbo.ID, dbo.Name FROM  dbo.MyTable

GO

How to check table exist in sql server?

For checking the table exist on not now its very simple code to check that information, We need to pass the table name in the object_id. That object_id  will return the information. Below code is simplest way to get the table exist or not in the sql server.

Query:

IF object_id('LiveData', 'U') is  null

    BEGIN

CREATE TABLE [dbo].[MyTable](

      [Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

      [Name] [varchar](50) NULL

)

END

How to check column name exists in sql server or not?

The latest SQL server we have a method name COL_LENGTH to get the table column information. The below two line of code is one of the easy way to get the column name exist in the table or not.

Query:

IF COL_LENGTH('MyTable','ColumnName') IS  NULL

 BEGIN

      ALTER TABLE MyTable

      ADD ColumnName int not null default(0)

 END

 GO

How to check stored procedure exist in sql server or not?

The simplest and easy way to check the stored procedure is exists or not using OBJECT_ID. If the stored procedure is exists we need to drop the procedure and recreate the same. Using this we can run multiple stored procedure at one time.

Query:

IF OBJECT_ID('SP_MYProcedure') IS NOT NULL

DROP PROC SP_MYProcedure

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SP_MYProcedure]

@ID varchar(50),

@Name varchar(10)

@Action varchar(50)

AS

BEGIN

       

if (@Action='GetData')

BEGIN

SELECT ID, Name FROM  MyTable WHERE ID=@ID   

END

END

How to run multiple create,alter and drop query in one single execution is to using GO key word we can do that. The Go Key word will split the query and its will execute one by one.

Example:

SELECT * from MYTABLE

GO

DROP TABLE MYTABLE

GO

CREATE MYTABLE………