What’s wrong with my subquery? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

What’s wrong with my subquery?

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-23 : 02:10:10

What’s wrong with my subquery?

--This SELECT list the table level extended properties on all tables in a schema.
SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, NULL, NULL)

TABLE MSA MS_Description Lists all MSA values.
TABLE ListName MS_Description This table contains a list of …
TABLE County MS_Description Lists all counties in the US.
TABLE ContactInfo MS_Description Lists the methods of …

--This SELECT list the column level extended properties on all tables in a schema.
SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'ADDRESS', 'column', default)

COLUMN AddressID MS_Description Primary key - Unique identification number …
COLUMN ListID_TimeZone MS_Description Foreign key to List.ListID, …
COLUMN ListID_StateProvince MS_Description Foreign key to List.ListID…
COLUMN ListID_LLResolution MS_Description Foreign key to List.ListID, …

I want to create a SELECT with a subquery to combine the two results into one. A result like this...

TABLE MSA MS_Description Lists all MSA values COLUMN AddressID MS_Description Primary key - Unique identification number …

This was my last attempt at writing the SELECT

SELECT t.objname, t.value,c.objname, t.value FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, NULL, NULL) t
inner join (SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'ADDRESS', 'column', default)) c
on t.objname = c.objname

I don’t get an error but it returns no rows. Can some one show me where I’m going wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-23 : 03:22:22

How can you join on t.objname=c.objname when t.objname contains table names and c.objname contains column names of a table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-23 : 04:00:21

The only solution i could find for your request was using dynamic SQL

CREATE PROC GetTableMetadata
@TableName varchar(100)
AS
DECLARE @Sql varchar(7000)
SET @Sql='SELECT * FROM (
SELECT *
FROM ::fn_listextendedproperty (NULL, ''user'', ''dbo'', ''table'', default, NULL, NULL)) t1

INNER JOIN (
SELECT *,'''+@TableName+''' AS TABLE_NAME
FROM ::fn_listextendedproperty (NULL, ''user'', ''dbo'', ''table'', '''+@TableName +''', ''column'', default))t2
ON t1.objname=t2.TABLE_NAME'

Exec(@Sql)
GO

now call this procedure passing a table name to get all the below requested details

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-23 : 13:54:51

Thank you very much. I am very new to this and now I have a better understanding of how to put together subqueries. In the end I am trying to build a data dictionary. To do that there are two more sets of data I need to add:
1. The rest of the column metadata using SELECT * FROM INFORMATION_SCHEMA.columns
2. Add a count column that counts the number of populated rows in the column.

I attempted to add the remaining column metadata as follows:

SELECT * FROM (
SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, NULL, NULL)) t

INNER JOIN (
SELECT *, 'address' AS 'TABLE_NAME'
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'address', 'column', default))c
ON t.objname=c.TABLE_NAME

INNER JOIN (
SELECT *
FROM INFORMATION_SCHEMA.columns)d
ON t.objname=d.TABLE_NAME

But I get the following error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

Why would the collations be different in INFORMATION_SCHEMA? All this data is coming from Master.Is ther a way to correct this?

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-23 : 22:21:11

I found the problem.

select * from
(SELECT 'lists' AS 'TABLE_NAME', *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'Lists', 'column', default))c
INNER JOIN
(SELECT * FROM INFORMATION_SCHEMA.columns)d
ON c.objname = d.column_name COLLATE Latin1_General_CI_AI
AND c.TABLE_NAME = d.TABLE_NAME
ORDER BY d.Ordinal_Position
-- INFORMATION_SCHEMA.columns has a collation of "SQL_Latin1_General_CP1_CI_AS"
-- fn_listextendedproperty has a collation of "Latin1_General_CI_AI"
-- The system Master tables are using a different collation than the created DB's. Use the
-- COLLATE option to cast the collation in the query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources