• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1334
  • Last Modified:

SQL check if table exists

I have some dynamic SQL - below - what I need to do is check if the table exists in the remote db - if so use the below - else, I will use something else.  The db  is dynamically passed n as a param (@strDB)
set @strSQL = 'SELECT NULL, ' + CAST(@contractID as varchar(20)) + ', PATIENT_TP, CASE WHEN MAX(REIMB_TP) = ''FIXED'' THEN 4 WHEN MAX(REIMB_TP) = ''VARIABLE'' THEN 5 END, 
SUM(CHARGES), MAX(BD_CONTRACT), 0, (100-MAX(BD_CONTRACT)), MAX(REIMB_TP) 
FROM data8.' + @strDB + '.dbo.tblREIMBURSEMENT WHERE insurance = ' + @ID + ' GROUP BY PATIENT_TP'

Open in new window

0
tbaseflug
Asked:
tbaseflug
1 Solution
 
tpi007Commented:
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='tablename')
SELECT 'tablename exists.'
ELSE
SELECT 'tablename does not exist.'
0
 
tbaseflugAuthor Commented:
How do I check it against a database when the database name is a param being passed in?  I assume that it will be some mix of dynamic sql, etc...?
0
 
tbaseflugAuthor Commented:
This is what I am trying - but for some reason it still tries to select against the table in question - and errors out, etc.
DECLARE @tmp0 TABLE ([reimbID] int, [contractID] varchar(50), [patientType] varchar(1),
[reimbursementMethod] int, [totalCharge] money, [chargePercentageDiscount] numeric(10,2), 
[claimCap] money, [cashContributionMargin] numeric(10,2), [settlementMethod] varchar(50))
 
DECLARE @contractID int, @ID varchar(50)
SET @contractID = 123
DECLARE @strDB varchar(100)
declare @strSQL nvarchar(max)
SET @ID = (SELECT contractNumber FROM CM_tblContractSummary WHERE contractID = @contractID)
 
SET @strDB = 'dbTexasHealthResources'
set @strSQL = 'IF EXISTS (SELECT 1 
    FROM DATA8.' + @strDB + '.INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE=''BASE TABLE''
    AND TABLE_NAME=''tblREIMBURSEMENT'') BEGIN 
      SELECT NULL, ' + CAST(@contractID as varchar(20)) + ', PATIENT_TP, CASE WHEN MAX(REIMB_TP) = ''FIXED'' THEN 4 WHEN MAX(REIMB_TP) = ''VARIABLE'' THEN 5 END, 
SUM(CHARGES), MAX(BD_CONTRACT), 0, (100-MAX(BD_CONTRACT)), MAX(REIMB_TP) 
FROM data8.' + @strDB + '.dbo.tblREIMBURSEMENT WHERE insurance = ' + @ID + ' GROUP BY PATIENT_TP END
ELSE BEGIN
       SELECT NULL, ' + CAST(@contractID as varchar(20)) + ', NULL, NULL, NULL, NULL, NULL, NULL, NULL  END'
 
INSERT INTO @tmp0
exec sp_executesql @strSQL
 
SELECT * FROM @tmp0

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
ralmadaCommented:
what if you use
SET @STRsql = 'IF OBJECT_ID (N''DATA8.'' + @STRdb + ''dbo.tblREIMBURSEMENT'', N''U'') IS NOT NULL
BEGIN
.....
0
 
Igor-KCommented:
You cannot check table existance and execute query agains it in one go if you do not use dinamic sql.
Partition this query into two and execute second one if the first one returns success, or use encapsulated dynamic sql inside other dynamic sql like :

declare @cmd nvarchar(2000),@db nvarchar(20)
set @db = 'master'

set @cmd = 'if exists(select * from '+@db+'..sysobjects where name = ''sysusers'') begin
declare @m nvarchar(2000)
set @m=''select 1 from '+@db+'..sysusers''
exec (@m)
end
else
select ''NO'''
print @cmd
exec (@cmd)
0
 
Igor-KCommented:
The reason for that is you receive compilation error when the object does not exist before SQL server tries to execute your check syntax
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now