SysViews and Systables

10 Sep 2005 10:54 AM

Madhivanan

When I want to view all the table names and column names
I used following two queries

Select table_name, Column_name from information_Schema.columns
order by table_name

Select object_name(so.id),sc.name from sysobjects so inner join
syscolumns sc
on so.id=sc.id order by so.name

Both produce the same results

But the execution plan says first query takes 67.92% cost and second
takes 32.08%

I think that Views are faster than tables when quering.

Why is this difference?

Madhivanan

10 Sep 2005 11:21 AM

Mike Epprecht (SQL MVP)

Hi

The views are build up differently and do more work thank the sys* tables.

Rather use the new views that will work in SQL Server 2005 too:
INFORMATION_SCHEMA.COLUMNS
and
INFORMATION_SCHEMA.TABLES

--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"Madhivanan" <madhivanan2***@gmail.com> wrote in message
news:1126349687.013679.263330@o13g2000cwo.googlegroups.com...
> When I want to view all the table names and column names
> I used following two queries
>
> Select table_name, Column_name from information_Schema.columns
> order by table_name
>
> Select object_name(so.id),sc.name from sysobjects so inner join
> syscolumns sc
> on so.id=sc.id order by so.name
>
> Both produce the same results
>
> But the execution plan says first query takes 67.92% cost and second
> takes 32.08%
>
> I think that Views are faster than tables when quering.
>
> Why is this difference?
>
> Madhivanan
>