MS SQL查询—如果不存在表

I make a cursor (MSSQL):

我做一个游标(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.

显而易见，因为KNF_C数据库没有这个表。我希望整个游标处理这样的异常。

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

请帮忙解决

2 个解决方案

#1

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.

您需要做的是动态调用sysobjects表，以获取表是否存在，然后有条件地选择您想要插入到临时表中的内容。

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

#2

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

在SQL Server上，您可以使用OBJECT_ID检查对象是否存在:

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

Or use INFORMATION_SCHEMA.TABLES instead of sys.databases.

或使用INFORMATION_SCHEMA。表而不是sys.databases。

#1