How to select table info? - SQL Server Forums

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)

How to select table info?

Author

Topic

Antinsh
Starting Member

16 Posts

Posted - 2008-04-07 : 12:39:21

Hi

Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?

something like:

SELECT
TABLE_NAME
,HAS_PRIMARY_KEY
,KEY_HAS_IDENTITY_SPECIFICATION
FROM
...this is where you come in
ORDER BY
TABLE_NAME

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-07 : 12:58:29

Have a look at system views in INFORMATION_SCHEMA to obtain this:-

http://developer.mimer.com/documentation/html_91/Mimer_SQL_Engine_DocSet/Data_dic_views2.html

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-04-07 : 12:59:41

There are probably better ways to do this but ...

select
[TABLE_NAME] = OBJECT_NAME(obj.object_id)
,[HAS_PRIMARY_KEY] = max(convert(int,ind.is_primary_key ))
,[KEY_HAS_IDENTITY_SPECIFICATION] = max(convert(int,col.is_identity))
from
sys.objects obj
inner join
sys.indexes ind
on
obj.object_id = ind.object_id
INNER JOIN
sys.columns col
on
obj.object_id = col.object_id

where obj.TYPE = 'U'

group by OBJECT_NAME(obj.object_id)

order by table_name

Jim

Antinsh
Starting Member

16 Posts

Posted - 2008-04-08 : 06:22:32

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 06:46:15

quote:
Originally posted by visakh16

Have a look at system views in INFORMATION_SCHEMA to obtain this:-

http://developer.mimer.com/documentation/html_91/Mimer_SQL_Engine_DocSet/Data_dic_views2.html

Do you think it is a great idea to supply information about MIMER documentation about INFORMATION_SCHEMA system views, rather than Microsoft information found in Books Online?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 11:27:33

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

Have a look at system views in INFORMATION_SCHEMA to obtain this:-

http://developer.mimer.com/documentation/html_91/Mimer_SQL_Engine_DocSet/Data_dic_views2.html

Do you think it is a great idea to supply information about MIMER documentation about INFORMATION_SCHEMA system views, rather than Microsoft information found in Books Online?

E 12°55'05.25"
N 56°04'39.16"

I think it has enough information to start on INFORMATION_SCHEMA system views. Anyways I found it really helpful.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 11:31:22

http://msdn2.microsoft.com/en-us/library/ms186778.aspx

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources