Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Question Unanswered: Getting a max() from EVERY table...

    I'd like to be able to get the maximum value of a column present in most of our tables (let's call it X_IDNTY).

    Can someone think of a query, that would produce output like:

    max(X_IDNTY) objectname
    -------------- -----------
    ........................................

    I can only imagine going through every table... Any ideas?

    Thanks!

  2. #2
    Join Date
    Sep 2005
    Location
    France, Paris
    Posts
    34
    Hello,

    Maybe you can retrive the name of the tables into a temporary table, then in a loop use the 'execute' command to run a select max(column) from table.

    Roughly something like this:

    if exists(select name from tempdb..sysobjects where name = "tmp_dba_table")
    begin drop table tempdb..tmp_dba_table end

    declare @owner varchar(30),
    @objname varchar(30),
    @target varchar(61)

    select name,owner = user_name(uid) into tempdb..tmp_dba_table from sysobjects where type = 'U'

    while (select count(*) from tempdb..tmp_dba_table)<>0
    begin
    set rowcount 1
    select @objname = name, @owner = owner from tempdb..tmp_dba_table
    select @target = @owner+"."+@objname,
    execute ('select max(X_IDNTY) from '+@target)

    delete from tempdb..tmp_dba_table
    set rowcount 0
    end

    I didn't test it ( ) but hope this will give you inspiration
    Regards.

  3. #3
    Join Date
    Apr 2003
    Posts
    64
    How about a single query (with possible subqueries)? Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •