July 9, 2010 at 12:07 am
Comments posted to this topic are about the item 1NF View Generator
July 9, 2010 at 6:30 am
Thanks Mark,
Interesting idea and implementation. I think it's a pity you didn't put a little more text around it, add an example and submit it as an article rather than a script. (It's not too late.)
Regards,
David.
July 9, 2010 at 6:59 am
Definitely useful!
First table I tried was in a non-default schema and the script didn't like that. I'll see if I can add a second parameter for schema. Also, if the referenced tables are in a different schema, the script assumes they are the same script as the examined table. We commonly put our look-up tables in a different schema to assist in migrating data across test regions.
July 9, 2010 at 8:49 am
I think this script will be extremely useful, but are you sure it works? I have AdventureWorks running on a SQL 2008 box, and I copied and pasted the code from the site, which erred out. I'm not really sure what is wrong with the code. The first error line is pointing to this line -
??+ 'SELECT /* ' + CONVERT(VARCHAR, Getdate(), 100) + ' */' + CHAR(10).
Am I doing something wrong? Can you offer any assistance?
Msg 102, Level 15, State 1, Line 130
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 133
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 153
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 159
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 208
Incorrect syntax near 'END'.
July 9, 2010 at 9:05 am
MattW2010 (7/9/2010)
I think this script will be extremely useful, but are you sure it works? I have AdventureWorks running on a SQL 2008 box, and I copied and pasted the code from the site, which erred out. I'm not really sure what is wrong with the code. The first error line is pointing to this line -??+ 'SELECT /* ' + CONVERT(VARCHAR, Getdate(), 100) + ' */' + CHAR(10).
Am I doing something wrong? Can you offer any assistance?
Msg 102, Level 15, State 1, Line 130
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 133
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 153
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 159
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 208
Incorrect syntax near 'END'.
When copying/pasting from the web page, I had to remove the leading indentation. It's the white space that is causing these errors.
July 9, 2010 at 9:14 am
That worked! Thanks. And the script is pretty darn awesome!!!
July 9, 2010 at 9:19 am
David McKinney (7/9/2010)
Thanks Mark,Interesting idea and implementation. I think it's a pity you didn't put a little more text around it, add an example and submit it as an article rather than a script. (It's not too late.)
Regards,
David.
I should have added, Mark, that my suggestion of an article is principally to give the script / idea the audience it no doubt deserves (but also opens you up to much more detailed critiques!)
July 16, 2010 at 3:28 pm
I like the script; I had to make a small exchange to get it to work correctly. In the subquery where the temp table #tmpjoins is generated, I had to swap the parameters between the REFERENCING_SCHEMA and the REFERENCED_SCHEMA. After the swap was made everything worked as designed. While I was at it I also update the query that generates the temp table to use the new Catalog views. I have it posted here.
Tom
SELECT
REFERENCING_SCHEMA,
REFERENCING_TABLE,
REFERENCING_COL,
REFERENCED_SCHEMA,
REFERENCED_TABLE,
REFERENCED_COL,
TABLE_ALIAS,
( CASE
WHEN SUB_QUERY.is_nullable = 1 THEN ' LEFT OUTER JOIN '
ELSE ' INNER JOIN '
END )
+ REFERENCED_SCHEMA + '.' + REFERENCED_TABLE + ' AS ' + TABLE_ALIAS
+ ' ON ' + REFERENCING_TABLE + '.' + REFERENCING_COL
+ '=' + TABLE_ALIAS + '.' + REFERENCED_COL AS JOIN_SYNTAX
INTO #tmpjoins
FROM (SELECT
Object_name(f.constraint_column_id) AS CONSTRAINT_NAME, -- f.constid
Object_schema_name(f.parent_object_id) AS REFERENCING_SCHEMA,
Object_name(f.parent_object_id) AS REFERENCING_TABLE,
c1.name AS REFERENCING_COL,
c1.is_nullable,
Object_schema_name(f.referenced_object_id) AS REFERENCED_SCHEMA,
Object_name(f.referenced_object_id) AS REFERENCED_TABLE,
c2.name AS REFERENCED_COL,
( CASE /* if colname of both tables same, use actual tab name, else use colname for alias */
WHEN c1.name = c2.name THEN Object_name(f.referenced_object_id)
ELSE c1.name
END ) AS TABLE_ALIAS
FROM sys.foreign_key_columns f
JOIN sys.columns c1 ON c1.Object_id = f.parent_object_id
AND c1.column_id = f.parent_column_id
JOIN sys.columns c2 ON c2.Object_id = f.referenced_object_id
AND c2.column_id = f.referenced_column_id
) SUB_QUERY ;
July 21, 2010 at 3:14 am
Yeap, pretty cool, thx 😎
But in my real world scenarios, i need get references from referenced tables too. But this is a nice peace of work to start 🙂
May 18, 2016 at 1:09 pm
Thanks for the script.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply