CODEWITHSUNDEEP
sqlsybasesap-iq
deepaksingh pawar
deepaksingh pawar

Reputation: 110

Querying other shema syscolumns table

I am executing SQL query to check weather schema abc_hist has table @table_name or not. but following query fails to return any result even when table exist i.e., which make if condition false every time:

use abc
go
----procedure---
IF EXISTS(select  1
                  from    abc_hist..syscolumns
                  where   status & 128 = 128
                          and     object_name(id) = @table_name )
----procedure---

So, Question is there any other way to effective check table existence in other schema or correction in my current sql ?

Upvotes: 0

Views: 38

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

Run this:

select 
  CASE WHEN status & 8> 0 THEN 'allows null' ELSE 'no nulls' end, 
  CASE WHEN status & 16 > 0 THEN 'check constraint exists' ELSE 'no checks' end, 
  CASE WHEN status & 128 > 0 THEN 'is identity' ELSE 'not identity' end,
  *
              from    abc_hist..syscolumns
              where   object_name(id) = @table_name

It will say "not identity" in every row, which means the @table_name you passed in as a parameter has no identity columns, and because you made it a condition of your where clause that the results must be an identity column in order to be returned, there are no results, so EXISTS is always false

Remove the WHERE clause on status if you want to use this query to check on a table's existence:

IF EXISTS(select  1
              from    abc_hist..syscolumns
              where   object_name(id) = @table_name )

Upvotes: 1

Related Questions