List tables and fields (not views)

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 2005 Forums

Transact-SQL (2005)

List tables and fields (not views)

Author

Topic

smithersgs
Starting Member

17 Posts

Posted - 2008-10-14 : 10:05:34

Hi, I have a script that returns a list of tables and fields in a database, but it returns Views as well. I want to get a list of tables only. Below is the script. How should I change it? Thanks.

select table_name, column_name, ordinal_position, data_type
from information_schema.columns
order by 1,3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 10:09:23

[code]SELECT c.*
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_TYPE = 'BASE TABLE'[/code]

E 12°55'05.63"
N 56°04'39.26"

smithersgs
Starting Member

17 Posts

Posted - 2008-10-14 : 10:21:00

quote:

Originally posted by Peso

SELECT		c.*
FROM		INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN	INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_CATALOG = c.TABLE_CATALOG
			AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
			AND t.TABLE_NAME = c.TABLE_NAME
			AND t.TABLE_TYPE = 'BASE TABLE'

E 12°55'05.63"
N 56°04'39.26"

Thanks Peso, I am sorry, one more thing. How should change it only for user tables excluding system tables?
Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 11:27:14

[code]select c.name,t.name,..
from sys.objects t
join sys.columns c
on c.object_id=t.object_id
where OBJECTPROPERTY(t.object_id,'IsMSShipped')=1
and t.type='U'[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 11:33:19

Is there anything else we should know of?

E 12°55'05.63"
N 56°04'39.26"

smithersgs
Starting Member

17 Posts

Posted - 2008-10-14 : 11:56:52

quote:

Originally posted by visakh16

select c.name,t.name,..
from sys.objects t
join sys.columns c
on c.object_id=t.object_id
where OBJECTPROPERTY(t.object_id,'IsMSShipped')=1
and t.type='U'

visakh16,
Your query returns MSmerge_**** and sysmerge**** tables, not user tables??

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-14 : 12:02:47

select t.name,c.name
from sys.objects t
join sys.columns c
on c.object_id=t.object_id
where t.type ='U'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 12:45:15

quote:
Originally posted by smithersgs

quote:
Originally posted by visakh16

select c.name,t.name,..
from sys.objects t
join sys.columns c
on c.object_id=t.object_id
where OBJECTPROPERTY(t.object_id,'IsMSShipped')=1
and t.type='U'

visakh16,
Your query returns MSmerge_**** and sysmerge**** tables, not user tables??

ah i had it other way round it should be this

]

select c.name,t.name,..
from sys.objects t
join sys.columns c
on c.object_id=t.object_id
where OBJECTPROPERTY(t.object_id,'IsMSShipped')=0and t.type='U'

smithersgs
Starting Member

17 Posts

Posted - 2008-10-14 : 16:30:14

That's it. Thanks visakh16.

FYI. sodeep, your query returns some system tables as well. It returns exactly the same result as Peso's.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-14 : 18:41:18

quote:
Originally posted by smithersgs

That's it. Thanks visakh16.

FYI. sodeep, your query returns some system tables as well. It returns exactly the same result as Peso's.

Well, it doesn't return for me.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-15 : 03:08:22

quote:
Originally posted by smithersgs

quote:
Originally posted by Peso

SELECT c.*
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_TYPE = 'BASE TABLE'

E 12°55'05.63"
N 56°04'39.26"

Thanks Peso, I am sorry, one more thing. How should change it only for user tables excluding system tables?
Thanks,

Did you execute the query?
It would give you only user defined tables

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 04:32:38

quote:
Originally posted by madhivanan

quote:
Originally posted by smithersgs

quote:
Originally posted by Peso

SELECT c.*
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_TYPE = 'BASE TABLE'

E 12°55'05.63"
N 56°04'39.26"

Thanks Peso, I am sorry, one more thing. How should change it only for user tables excluding system tables?
Thanks,

Did you execute the query?
It would give you only user defined tables

Madhivanan

Failing to plan is Planning to fail

may be he tweaked where part

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources