Sql Database Schema - 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

General SQL Server Forums

New to SQL Server Programming

Sql Database Schema

Author

Topic

twinklestar0802
Starting Member

17 Posts

Posted - 2005-10-24 : 00:26:33

Hi. I would like to retrieve the table names of a database, the column names nad its contraints of each table in a database.. How can this be achieved???

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-10-24 : 00:45:24

Hi,
you may fire the query as :

select * from information_schema.tables where table_catalog ='dpms'

select * from INFORMATION_SCHEMA.COLUMNS

to get the desired output .

-----------------------------------------------------------
MSSQL Server encryption software http://www.activecrypt.com

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-10-24 : 00:49:00

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 01:23:26

Try this also

select CU.table_name, CU.Column_Name,CU.constraint_Name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on CU.Constraint_Name=TC.Constraint_Name

Madhivanan

Failing to plan is Planning to fail

twinklestar0802
Starting Member

17 Posts

Posted - 2005-10-24 : 01:36:09

Hi, Thank you very much to those who replied but I do not understand.
Can you guys give me an example? I have a northwind database.. I want to get all the tables and columns of each tables with their contraints specifying if they are primary or foreign keys...
Can you guys give me an example

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-10-24 : 01:42:45

Hi,

select * from information_schema.tables where table_catalog ='northwind'
go
use northwind
go
select * from INFORMATION_SCHEMA.COLUMNS
go

HTH

-----------------------------------------------------------
MSSQL Server encryption software http://www.activecrypt.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 01:50:55

Use northwind

select CU.table_name, CU.Column_Name,CU.constraint_Name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on CU.Constraint_Name=TC.Constraint_Name

Madhivanan

Failing to plan is Planning to fail

twinklestar0802
Starting Member

17 Posts

Posted - 2005-10-24 : 02:07:17

Hi I just got it.. Thanks guys...

What does Information_Schema mean? Where can i get more information on this?

I also want to know how to determine if it allows null values and what kind of data type it is...

Please help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 02:13:10

>>What does Information_Schema mean?

Its the owner of the System Views
See more info in Books On Line, SQL Server help file

>>I also want to know how to determine if it allows null values and what kind of data type it is...

Did you run this query?
select * from INFORMATION_SCHEMA.COLUMNS where table_Name='YourTable'

Madhivanan

Failing to plan is Planning to fail

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-10-24 : 02:14:31

Hi,
suggest to read it in BOL you will get almost all you wants .

HTH

-----------------------------------------------------------
MSSQL Server encryption software http://www.activecrypt.com

twinklestar0802
Starting Member

17 Posts

Posted - 2005-10-25 : 03:27:22

Hi.. I did it but I have results which I cant understand..
I tried it with the pubs database. The constraints column gave me results such as UPKCL_titleidind, FK__titleauth__au_id__0519C6AF and
PK__jobs__117F9D94.. What do they mean??

This is my query thanks to the people who helped me:

----

SELECT T.TABLE_NAME AS [Table], COL, DATATYPE, NULLABLE, DEFAULTVALUE, MAXIMUM,
CONSTRAINTS

FROM INFORMATION_SCHEMA.Tables T

LEFT OUTER JOIN
(
SELECT INFORMATION_SCHEMA.Columns.COLUMN_NAME AS COL,
iNFORMATION_SCHEMA.Columns.TABLE_NAME AS TAB,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME AS CONSTRAINTS,
INFORMATION_SCHEMA.Columns.COLUMN_DEFAULT AS DEFAULTVALUE,INFORMATION_SCHEMA.Columns.IS_NULLABLE AS NULLABLE,
INFORMATION_SCHEMA.Columns.DATA_TYPE AS DATATYPE,INFORMATION_SCHEMA.Columns.CHARACTER_MAXIMUM_LENGTH AS MAXIMUM

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
RIGHT JOIN INFORMATION_SCHEMA.Columns
ON iNFORMATION_SCHEMA.Columns.COLUMN_NAME=INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.COLUMN_NAME
AND
iNFORMATION_SCHEMA.Columns.TABLE_NAME=INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_NAME
)

DATABASE_INFO ON
DATABASE_INFO.TAB=T.TABLE_NAME

WHERE T.TABLE_NAME NOT LIKE 'sys%'
AND T.TABLE_NAME <> 'dtproperties'
AND T.TABLE_SCHEMA <> 'INFORMATION_SCHEMA'
AND T.TABLE_TYPE!='VIEW'
ORDER BY T.TABLE_NAME

----

Can someone also edit the query for me? I want to combine and foreign key and primary key constraints into one row.. This is the result of my current query:
Example:
Column Constraints
au_id PK
au_id FK

I want to do this: (Can someone help me?)

Column Constraints
au_id PK and FK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-25 : 04:09:01

Why do you want to do this?
Where do you want to show these data?

Madhivanan

Failing to plan is Planning to fail

twinklestar0802
Starting Member

17 Posts

Posted - 2005-10-25 : 05:52:09

I am making a program that will store data in the database. My program will put all of the columns and tables in a list for reference...

Whenever the user inputs a certain data for a specific field, I check the validity through the constraints, nullable etc...

I want to put the FK and PK in a single column

Kristen
Test

22859 Posts

Posted - 2005-10-25 : 06:37:26

"The constraints column gave me results such as UPKCL_titleidind, FK__titleauth__au_id__0519C6AF and
PK__jobs__117F9D94.. What do they mean?? "

You didn't provide a constraint name when you created the object - so SQL Server allocated a "computer generated" name

They are a nuisance because, assuming you used a script, rather than the GUI AND you want to run the script on multiple servers (e.g. Development, Test, Production) then each server will have a different name for the constraint ... so when you want to drop it you won't be able to have a script that runs on all the servers [well, you can, but it will be harder than if they had a human-allocated name]

Kristen

twinklestar0802
Starting Member

17 Posts

Posted - 2005-10-25 : 23:42:01

TNX!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources