Post reply

SQL syntax please ?

  • December 9, 2015 at 8:27 am

    #323170

    I have a table that got created in a schema ( I didn't add that but the schema came with it when it was imported from another database )

    Select * FROM CMS.STHMCDPTD;

    My question:

    I like to write a statement to check the presence of this table.

    So when I do the following I can get tat info.

    Select * FROM sys.tables where name like '%STHMCDPTD%' -- returns one row but the name field shows only STHMCDPTD

    Select * FROM sys.tables where name = 'CMS.STHMCDPTD' -- no rows returned

    You get the point here...

    I am trying to check for the presence of a table CMS.STHMCDPTD and not

    just STHMCDPTD.

    I can certainly modify that SELECT stmt to include SCHEMA_ID = 6

    But then not I don't want to hard code the 6. The schema ID can change.

    So some of you may now how to join the above select stmt with another system table or view and get what I want.

    Amen

    Thanks

  • December 9, 2015 at 8:36 am

    #1844930

    You might need this:

    SELECT *

    FROM sys.tables t

    JOIN sys.schemas s ON t.schema_id = s.schema_id

    where t.name = 'STHMCDPTD'

    AND s.name = 'CMS'

    Or this:

    SELECT *

    FROM sys.tables t

    where t.name = 'STHMCDPTD'

    AND SCHEMA_NAME(t.schema_id) = 'CMS'

    Or for a simple quick check of existence, you can validate the existence using OBJECT_ID()

    IF OBJECT_ID('CMS.STHMCDPTD') IS NOT NULL

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply