stored procedure to check if table present in sql

Posted by Chaithragm under Sql Server on 1/31/2014 | Points: 10 | Views : 797 | Status : [Member] | Replies : 2
stored procedure to check if table is present in the schema by taking the table name ,if not it has to create the table by same name




Responses

Posted by: Bandi on: 1/31/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--Script for checking table in the given schema and create the same if not available
Declare @TabName VARCHAR(50) = 'Table1', @Schema VARCHAR(30) = 'dbo', @SQL VARCHAR(max) =''
IF NOT EXISTS (SELECT 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @Schema AND TABLE_NAME = @TabName )
BEGIN
SET @SQL = 'CREATE TABLE ' + @Schema + '.' + @TabName + '(col1 int);'
EXEC (@SQL)
END
GO
--Check for table: Table1
select * FROM Table1


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Chaithragm, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: kgovindarao523-21772 on: 1/31/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi,

Your Stored Procedure may like this
Please Mark as Answer if you got this

--CREATE/ALTER Here
CREATE PROCEDURE sp_ISTABLEEXISTED
(
@inputTABLENAME VARCHAR(50)
)
AS
BEGIN
DECLARE @CREATETABLE VARCHAR(MAX)
DECLARE @COLUMNAMES VARCHAR(MAX)
SET @CREATETABLE='[dbo].['+@inputTABLENAME+']'

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(@CREATETABLE) AND type in (N'U'))
BEGIN
PRINT 'Table Existed'
END

ELSE
BEGIN
SET @CREATETABLE=''
SET @CREATETABLE='CREATE TABLE '+@inputTABLENAME
SET @COLUMNAMES='ID INT' --Specify columns Here
EXEC(@CREATETABLE)
END
END


Thank you,
Govind

Chaithragm, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response