Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

1NF View Generator Expand / Collapse
Author
Message
Posted Friday, July 9, 2010 12:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 8:47 AM
Points: 3, Visits: 35
Comments posted to this topic are about the item 1NF View Generator
Post #949743
Posted Friday, July 9, 2010 6:30 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 645, Visits: 1,842
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.
Post #949904
Posted Friday, July 9, 2010 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 22, 2013 3:43 PM
Points: 23, Visits: 183
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.
Post #949930
Posted Friday, July 9, 2010 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:40 AM
Points: 58, Visits: 396
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'.
Post #949998
Posted Friday, July 9, 2010 9:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 22, 2013 3:43 PM
Points: 23, Visits: 183
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.
Post #950012
Posted Friday, July 9, 2010 9:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:40 AM
Points: 58, Visits: 396
That worked! Thanks. And the script is pretty darn awesome!!!
Post #950022
Posted Friday, July 9, 2010 9:19 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 645, Visits: 1,842
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!)
Post #950029
Posted Friday, July 16, 2010 3:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 29, 2011 11:01 AM
Points: 14, Visits: 51
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 ;
Post #954219
Posted Wednesday, July 21, 2010 3:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:30 AM
Points: 161, Visits: 345
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
Post #956191
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse