Column Count from Derived Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Column Count from Derived Table

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2010-02-22 : 12:17:01

select count(*) from information_schema.columns
where table_name='abc'

How do I get this also from a derived table below? I just want to make sure the multiple tables have the same column count. Each must be the same, otherwise there'd be an error doing the union...

select count(*) from information_schema.columns
where table_name='t1'

select * from
(select * from ['130_ID2_JANUARY09_V5$']
union
select * from ['130_ID2_FEBRUARY09_V3$']
union
select * from ['130_ID1_MARCH09$']
union
select * from ['130_IP_APRIL_2009$']
union
select * from ['130_IP_MAY_2009$']
union
select * from ['130_IP_JUNE_2009$']
union
select * from ['130_SPIRE_IP_JULY09$']
union
select * from ['130_SPIRE_IP_AUG09_090909$']
union
select * from ['130_SPIRE_IP_SEPT09$']
union
select * from ['130_SPIRE_IP_OCT09_FINAL$']
union
select * from ['130_NOVEMBER_2009$']
union
select * from ['130_Dec09_Ramsay$']) t1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 12:32:29

If they don't have the same number and types of columns then your union-query will throw an error.
Maybe that is information enough?

No, you're never too old to Yak'n'Roll if you're too young to die.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2010-02-22 : 12:36:37

Why does everyone back out of answering a question??

I need to make sure the column count in the derived table matches with a seperate table, which is 112.

How do I get a column count from the derived table? Do I have to create a table of the derived, or can I get the count on the run????!?!?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 12:43:40

quote:
Originally posted by mikebird

Why does everyone back out of answering a question??

I need to make sure the column count in the derived table matches with a seperate table, which is 112.

How do I get a column count from the derived table? Do I have to create a table of the derived, or can I get the count on the run????!?!?!

because you're using union and merging results the column count in derived table will be equal to column count any one of tables involved (['130_ID2_JANUARY09_V5$'],['130_ID2_FEBRUARY09_V3$'],..)

so its enough to use below query to get column count

select count(*) from information_schema.columns
where table_name='130_ID2_JANUARY09_V5$'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 13:49:01

That is why my answer was:
If they don't have the same number and types of columns then your union-query will throw an error.

And if your union-query will throw an error then you can't do anything with that derived table.
And if your union-query will NOT throw an error then they have the same number of columns.

But I am very sorry mikebird.
In the future I will not back out If you have a question.
If I have a more concrete answer then I will post it else I will fall silent.

No, you're never too old to Yak'n'Roll if you're too young to die.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2010-02-23 : 04:18:44

visakh16

Thanks. I selected just one of those tables at a time in the union to get a column count. They show 0 columns using

select count(*) from information_schema.columns
where table_name='130_ID2_JANUARY09_V5$'

select * shows all the columns!!!

How can this be?!?!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 04:31:51

quote:
Originally posted by mikebird

visakh16

Thanks. I selected just one of those tables at a time in the union to get a column count. They show 0 columns using

select count(*) from information_schema.columns
where table_name='130_ID2_JANUARY09_V5$'

select * shows all the columns!!!

How can this be?!?!

Are you sure you are running in the same database?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 04:33:33

quote:
Originally posted by mikebird

select count(*) from information_schema.columns
where table_name='abc'

How do I get this also from a derived table below? I just want to make sure the multiple tables have the same column count. Each must be the same, otherwise there'd be an error doing the union...

select count(*) from information_schema.columns
where table_name='t1'

select * from
(select * from ['130_ID2_JANUARY09_V5$']
union
select * from ['130_ID2_FEBRUARY09_V3$']
union
select * from ['130_ID1_MARCH09$']
union
select * from ['130_IP_APRIL_2009$']
union
select * from ['130_IP_MAY_2009$']
union
select * from ['130_IP_JUNE_2009$']
union
select * from ['130_SPIRE_IP_JULY09$']
union
select * from ['130_SPIRE_IP_AUG09_090909$']
union
select * from ['130_SPIRE_IP_SEPT09$']
union
select * from ['130_SPIRE_IP_OCT09_FINAL$']
union
select * from ['130_NOVEMBER_2009$']
union
select * from ['130_Dec09_Ramsay$']) t1

Instead of *, you should always specify the column names. This way you can prevent any errors that occur when new columns are added to the tables

Madhivanan

Failing to plan is Planning to fail

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-02-23 : 04:34:13

What a horribly designed schema...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources