MS SQL query - if not exists a table

Question

I make a cursor (MSSQL):

CREATE TABLE #temp
(
      DB VARCHAR(50),
      Tab VARCHAR(50),
      [COUNT] INT
)

DECLARE @db_name NVARCHAR (150)
DECLARE @tab1 NVARCHAR (150)

set @tab1 = 'dbo.test'

DECLARE c_db_names CURSOR FOR

SELECT name 
FROM sys.databases
WHERE name like '%KNF%'

OPEN c_db_names

FETCH c_db_names INTO @db_name

WHILE @@Fetch_Status = 0
BEGIN
  EXEC('
    INSERT INTO #temp
    SELECT ''' + @db_name + ''',''' + @tab1 + ''',COUNT(*) FROM ' + @db_name + '.' + @tab1 + '

  ')
  FETCH c_db_names INTO @db_name
END

CLOSE c_db_names
DEALLOCATE c_db_names

SELECT * FROM #temp

DROP TABLE #temp

The cursor counts the number of rows in each database table. If there is no such table there is an error.

(1 row(s) affected)

(1 row(s) affected)
Msg 208, Level 16, State 1, Line 2
Invalid object name 'KNF_C.dbo.test'.

(2 row(s) affected)

What is obvious, because the KNF_C database does not have this table. I would like the entire cursor to handle such an exception.

The expected result:

DB      Tab         COUNT
KNF_A   dbo.test    3
KNF_B   dbo.test    7
KNF_C   no table

I know I should use syntax for example if exist but i dont know how.

Please help to solve this

McNets · Accepted Answer · 2017-04-20 17:11:55Z

4

On SQL Server you can check the existence of an object using OBJECT_ID:

IF OBJECT_ID('dbo.table_name') IS NOT NULL
  DO SOMETHING;

Or use INFORMATION_SCHEMA.TABLES instead of sys.databases.

answered Apr 20, 2017 at 17:11

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Avitus · Accepted Answer · 2017-04-20 17:42:18Z

2

What you have to do is dynamically call the sysobjects table to get if the table exists and then conditionally choose what you want to insert into your temp table.

This probably the code you need:

CREATE TABLE #temp
(
      DB VARCHAR(50),
      Tab VARCHAR(50),
      [COUNT] INT 
) 
DECLARE @str NVARCHAR(1000) 
DECLARE @db_name NVARCHAR (150)
DECLARE @tab1 NVARCHAR (150)
DECLARE @count INT

set @tab1 = 'dbo.test'

DECLARE c_db_names CURSOR FOR

SELECT name 
FROM sys.databases
WHERE name like '%KNF%'

OPEN c_db_names

FETCH c_db_names INTO @db_name

WHILE @@Fetch_Status = 0
BEGIN

        SET @str = N'Select @internalVariable = (select count(1) from ' + @db_name + '.dbo.sysobjects where name = ''' + replace(@tab1, 'dbo.','') + ''') '

        exec sp_executesql
            @str, --dynamic query
            N'@internalVariable int output', --query parameters
            @internalVariable = @count output --parameter mapping
  IF ( @count = 1 ) 
  BEGIN

      EXEC('
        INSERT INTO #temp
        SELECT ''' + @db_name + ''',''' + @tab1 + ''',COUNT(*) FROM ' + @db_name + '.' + @tab1 + '

      ')
  END
  ELSE 
  BEGIN
      EXEC('
        INSERT INTO #temp
        SELECT ''' + @db_name + ''',''no table'', null
      ')
  end

  FETCH c_db_names INTO @db_name
END

CLOSE c_db_names
DEALLOCATE c_db_names

SELECT * FROM #temp

DROP TABLE #temp

edited Apr 20, 2017 at 17:42

answered Apr 20, 2017 at 17:24

Avitus

16k6 gold badges47 silver badges54 bronze badges

6 Comments

Ebasse Over a year ago

Dont work: DB Tab COUNT KNF_A no table NULL KNF_B no table NULL KNF_C no table NULL

Avitus Over a year ago

it was because the sysobjects select needed to have the db_name. I have edited.

Ebasse Over a year ago

result is this same, but is a error "Invalid object name 'KNF_B.sysobjects'."

Avitus Over a year ago

it's because it was missing dbo. I have updated so it will be KNF_B.dbo.sysobjects

Ebasse Over a year ago

query is completed now without error, but all row in databases are "no table"

|

Collectives™ on Stack Overflow

MS SQL query - if not exists a table

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related