Post reply

SQL QUERY for table names based on column VALUE

  • November 21, 2012 at 10:36 am

    #265143

    Hi, I have a database where every table has an id column (CID) once this value changes all the tables must be checked and updated with the new CID value. I would like a write a query that returns all table names that DO or DO NOT have "CID = x" where x is the current CID value I define through a parameter. This way i can check and see what tables still need to be updated. Please help. Thanks.

  • November 21, 2012 at 10:57 am

    #1561328

    You can use sp_msforeachtable with a custom sql, something like this:

    http://www.sqlservercentral.com/Forums/Topic1038522-146-1.aspx

  • November 21, 2012 at 11:01 am

    #1561329

    nicholasferri (11/21/2012)

    Hi, I have a database where every table has an id column (CID) once this value changes all the tables must be checked and updated with the new CID value. I would like a write a query that returns all table names that DO or DO NOT have "CID = x" where x is the current CID value I define through a parameter. This way i can check and see what tables still need to be updated. Please help. Thanks.

    is CID a primary key in some main table, and the other CID columns are foreign keys (with real FK constraints on them?)

    you can get the collection of tables that reference another table via exec sp_fkeys myTableName, and build a process that follow those Foreign keys.

    you could modify the foreign keys to have ON UPDATE CASCADE to automatically populate the changes;

    if there is not a FK structure, you'd still have to read the metadata to find the columns one way or the other;

    SELECT object_name(object_id) As TalbeName,

    name As ColumnName ,

    column_id as ColumnOrder

    FROM sys.columns

    where name='CID'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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