Post reply

Finding the tables with no createdate

  • May 4, 2015 at 12:42 pm

    #296556

    Hi,

    In our Production db we have all most all tables have the column created or createdate.

    I need to find out all tables without the created or createdate column

    SELECT t8.name AS table_name,

    SCHEMA_NAME(schema_id) AS schema_name,

    c.name AS column_name

    FROM sys.tables AS t8

    INNER JOIN sys.columns c

    ON t8.OBJECT_ID = c.OBJECT_ID

    WHERE c.name not LIKE '%Created%'

    and c.name not exists in(SELECT distinct t8.name AS table_name,

    SCHEMA_NAME(schema_id) AS schema_name,

    c.name AS column_name

    FROM sys.tables AS t8

    INNER JOIN sys.columns c

    ON t8.OBJECT_ID = c.OBJECT_ID

    WHERE c.name not LIKE '%Created%')

    ORDER BY table_name;

  • May 4, 2015 at 1:21 pm

    #1795449

    Your code should be simpler and it shouldn't combine EXISTS and IN.

    SELECT t.name AS table_name,

    SCHEMA_NAME(schema_id) AS schema_name

    FROM sys.tables AS t

    WHERE not exists (SELECT 1

    FROM sys.columns c

    WHERE t.OBJECT_ID = c.OBJECT_ID

    AND c.name LIKE 'created%')

    ORDER BY table_name;

    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

  • May 4, 2015 at 4:39 pm

    #1795493

    Thank you

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

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