Skip to content
Advertisement

sys.tables vs COUNT

I have a variable @tableName (NVARCHAR(255)) that is name of a table. I’m trying to perform an action if data exists in the the table.

There are two ways to check this.

Method #1 (bulky)

DECLARE @rowsInTable BIGINT
DECLARE @cmd NVARCHAR(MAX)

SET @cmd = 'SELECT @rowsInTable = COUNT_BIG(*) from '+ @tableName
EXEC sp_executesql @cmd, N'@rowsInTable INT OUTPUT', @rowsInTable OUTPUT

IF (@rowsInTable > 0)
PRINT 'Do something' 

Method #2 (clean)

IF( EXISTS (SELECT * FROM sys.tables WHERE name = @tableName))
PRINT 'Do something' 
  • Are methods #1 and #2 doing the exact same thing?
  • Is it safe to use method #2? Can there be case when data exists is @tableName but method #2 would return false?
  • Is there a way I can make method #1 shorter? Or is there any third clean way to achieve this?

Advertisement

Answer

Method #1 can be optimized. Used of COUNT_BIG can be avoided. Have a look into below query.

DECLARE @dataExists int
DECLARE @cmd NVARCHAR(MAX)

SET @cmd = 'SELECT @dataExists = case when Exists(Select 1 from '+ @tableName+') then 1 else 0 end'
EXEC sp_executesql @cmd, N'@dataExists INT OUTPUT', @dataExists OUTPUT

IF (@dataExists > 0)
PRINT 'Do something' 

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement