SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


1NF View Generator


1NF View Generator

Author
Message
mark.stockwell
mark.stockwell
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 39
Comments posted to this topic are about the item 1NF View Generator
David McKinney
David McKinney
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2811 Visits: 2090
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.
Rob Nickolaus-860201
Rob Nickolaus-860201
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 189
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.
MattW2010
MattW2010
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 398
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'.
Rob Nickolaus-860201
Rob Nickolaus-860201
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 189
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.
MattW2010
MattW2010
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 398
That worked! Thanks. And the script is pretty darn awesome!!!
David McKinney
David McKinney
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2811 Visits: 2090
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!)
tfmaloney 52380
tfmaloney 52380
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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 ;
vevoda.ulath
vevoda.ulath
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 396
Yeap, pretty cool, thx Cool

But in my real world scenarios, i need get references from referenced tables too. But this is a nice peace of work to start :-)
Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22596 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search