Wednesday, September 20, 2006

Data Dictionary Queries for SQL Server and Oracle

For SQL Server

--Finding all details of Primary Key constraint
select * from sysobjectswhere xtype='PK'

--Finding all details of Foreign Key constraint
select * from sysobjectswhere xtype='F'

--Finding all User-Defined objects (tables, etc)
select * from sysobjectswhere xtype='U'

--Finding all System objects
select * from sysobjectswhere xtype='S'

--Finding all user names
select * from sysusers

--Finding Column Names of Particular Table
--Select Pubs Database
select c.name from sysobjects o, syscolumns cwhere o.id = c.id ando.name = 'publishers'



For ORACLE
select * from sys.dba_objectswhere owner = 'scott'and object_type='TABLE'
SELECT owner, object_name, object_type FROM sys.dba_objectswhere object_type='SEQUENCE' and owner='scott';

Today I was just trap with some queries and so the solution i come up with

2 comments:

DotNetGuts said...

For getting information about all tables in SQL Server:
SELECT * FROM INFORMATION_SCHEMA.TABLES

For getting information about all columns in SQL Server:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

DotNetGuts said...

For Getting Data Dictonary Information for User Defined Tables in SQL Server:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
where Table_Name in (select name from sysobjects where xtype='U')

/* NOTE
1 Copy The Result Data into Excel Sheet
2 Filter the data by AUTO FILTER
3 Now select Table Name and you will get relevant information accordingly
*/

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape