Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by mark.stockwell » 1NF View Generator
Add to briefcase

1NF View Generator Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
mark.stockwell
Posted Friday, July 09, 2010 12:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 8:47 AM
Points: 3, Visits: 35
Comments posted to this topic are about the item 1NF View Generator
Post #949743
David McKinney
Posted Friday, July 09, 2010 6:30 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601
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
Rob Nickolaus-860201
Posted Friday, July 09, 2010 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:07 PM
Points: 23, Visits: 180
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
MattW2010
Posted Friday, July 09, 2010 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:21 AM
Points: 57, Visits: 382
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
Rob Nickolaus-860201
Posted Friday, July 09, 2010 9:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:07 PM
Points: 23, Visits: 180
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
MattW2010
Posted Friday, July 09, 2010 9:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:21 AM
Points: 57, Visits: 382
That worked! Thanks. And the script is pretty darn awesome!!!
Post #950022
David McKinney
Posted Friday, July 09, 2010 9:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601
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
tfmaloney 52380
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
vevoda.ulath
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: Yesterday @ 2:33 AM
Points: 121, Visits: 288
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.