Re: help neeed statement

From: Ilya Margolin (ilya_at_unapen.com)
Date: 06/17/04 

Date: Thu, 17 Jun 2004 16:32:06 -0400

Darren,

...assuming they all have the same table definitions and the statement
length would not exceed 8000 characters:

declare @Stmt varchar(8000)

select @Stmt = @Stmt + ' union all select * from ' + name
from sysobjects
where name like '%Mail%'
    and type = 'U'

set @Stmt = right(@Stmt, len(@Stmt) - 11)

exec(@Stmt)

Ilya

"Darren Spooner" <DarrenS@ihatespam.spacecamp.com> wrote in message
news:OgJCoVKVEHA.2544@TK2MSFTNGP10.phx.gbl...
> i need to create a union select statement with all tables that have 'Mail'
> in the name of the table.
> i will have an unknow number of tables (Mail, Mail1, Mail2, Mail3, .....)
> how would i creat the statement?
>
>

Relevant Pages

  • Re: help neeed statement
    ... > from sysobjects ... >> i need to create a union select statement with all tables that have ... >> how would i creat the statement? ...
    (microsoft.public.sqlserver.programming)
  • Re: help neeed statement
    ... One way to do it, and I'm not saying this is the best, is to use something ... from sysobjects ... union all ... > how would i creat the statement? ...
    (microsoft.public.sqlserver.programming)
  • Re: len() malfunction?
    ... What objects have trailing spaces? ... INFORMATION_SCHEMA.ROUTINES instead of sysobjects? ... > I'm getting a returned column set with width of 128 characters even though ... > the longest name of all the stored procedures is only 44 characters. ...
    (microsoft.public.sqlserver.programming)
  • Re: Stipping all alpha characters from a string
    ... hi alyx, ... insert into t select 'call me' union all ... select strn, dbo.fn_numeric'numeric' ... > I need to strip the characters down to only a number so I can evalute ...
    (microsoft.public.sqlserver.mseq)
  • Re: RunTime Datatype Determination in C
    ... Richard Tobin wrote: ... a union of the possible types, ... "Consideration shall be given to the need for as many as 32 characters ... in some alphabets" - X3.4, ...
    (comp.lang.c)