subquery or joins - 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 2000 Forums

Transact-SQL (2000)

subquery or joins

Author

Topic

msdevcoder
Starting Member

4 Posts

Posted - 2009-03-27 : 12:06:19

Hi

I am trying to fetch system table and column related data in sql 2000. I have used a subquery to get column constraint types by joining few system tables. But i also need to get a count on the constraint types and constraint_name. But i cant include in the subquery. But it will look awkward if i write subquery to get each column i need to fetch. Any alternatives you can help?

my query below:
Select distinct
o.Name as TableName,
c.Name as ColumnName,
c.length as Length,
c.xprec as [Precision],
c.xscale as Scale,
c.isnullable,
(select name from sysTypes where xusertype = c.xusertype) as DataType,
CASE (select top 1 pk.constraint_type from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu where (constraint_type = 'FOREIGN KEY' or CONSTRAINT_TYPE = 'PRIMARY KEY') and cu.TABLE_NAME = pk.TABLE_NAME and cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME and pk.Table_Name = o.Name and cu.column_name=c.Name)
When 'PRIMARY KEY' then 'PK'
When 'FOREIGN KEY' then 'FK'
When 'Both' then 'Both'
ELSE 'None'
End as KeyType
-- Should i need to write another subquery like above to get the constraint_name or can be done through some kind of joins?
FROM
sysColumns C
INNER JOIN sysObjects o on c.id = o.id
WHERE
o.type = 'U' and o.name <> 'dtproperties'
ORDER BY o.name

Thanks
Gan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 15:02:38

Can you format your query ?? Align select, from, where clauses.

msdevcoder
Starting Member

4 Posts

Posted - 2009-03-27 : 16:33:15

-- formatted query (trailing space are getting truncated)
SELECT
distinct o.Name as TableName, c.Name as ColumnName, c.length as Length,
select name from sysTypes where xusertype = c.xusertype) as DataType,
CASE
(
SELECT top 1 pk.constraint_type FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
WHERE
(constraint_type = 'FOREIGN KEY' or CONSTRAINT_TYPE = 'PRIMARY KEY')
and cu.TABLE_NAME = pk.TABLE_NAME
and cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
and pk.Table_Name = o.Name and cu.column_name=c.Name
)
When 'PRIMARY KEY' then 'PK'
When 'FOREIGN KEY' then 'FK'
When 'Both' then 'Both'
ELSE 'None'
End as KeyType

-- Should i need to write another subquery like above to get the
constraint_name or can be done through some kind of joins?

FROM
sysColumns C
INNER JOIN sysObjects o on c.id = o.id
WHERE
o.type = 'U' and o.name <> 'dtproperties'
ORDER BY o.name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources