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.
| Author |
Topic |
|
Access
Starting Member
44 Posts |
 Posted - 2008-10-26 : 13:19:03
|
| I’m using what it’s called horizontal partitioning where I have a partitioning VIEW that basically UNION ALL needed tables.I’m creating the partitioning tables dynamically upon adding new affiliate (within administrative part of the application).Every time new table created I have to manually alter my view to UNION ALL new table that that just was created.I would like it to be as dynamic as possible.I’m looking for suggestion/advice on how to alter the view dynamically. The process will create a new table and right after alter the view to include this new table into it..Thanks |
|
|
Access
Starting Member
44 Posts |
Posted - 2008-10-27 : 10:06:00
|
| Got this from another forum if somebody interested:Well, you could query the sys.sql_modules system view to get the defintion of the view like this:Code SnippetSELECT definition FROM sys.sql_modules WHERE OBJECT_ID = OBJECT_ID('<yourViewNameHere') That will return the full create definiton of the view. You could then either drop and re-create the view or alter the view. Drop and re-create steps:-Drop the view-Set the result of the query above to a variable and append the additional syntax needed ie "UNION ALL SELECT a,b FROM newTable"-Execute the sql contained in the variableAlter steps:-Set the result of the query above to a variable. Do a replace on 'CREATE' and replace it with 'ALTER'-Append the new syntax needed for the new table-Execute the sql contained in the variable |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-10-27 : 10:30:47
|
| Modifying a vew dynamically isn't really something I'd recommend but adding tables dynamically isn't either so I guess you'll do fine...- Lumbago |
|
|
|
Access
Starting Member
44 Posts |
Posted - 2008-10-27 : 22:47:40
|
quote:
Originally posted by Lumbago
Modifying a vew dynamically isn't really something I'd recommend but adding tables dynamically isn't either so I guess you'll do fine...- Lumbago
Can you please explain why?Thanks |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-10-28 : 04:28:07
|
| Well...since I know nothing whatsoever about your business, the size of your database or anything about your requirements I can only give you my personal preferences, but I think adding tables dynamically removes a great deal of the control you have as a developer. For example; are you sure that all new affiliates will generate so many rows in as to defend giving it it's own unique table in your database? And what happens when/if your business grows out of proportions and you have hundreds or even thousands of affiliates? I have no idea if this is a likely scenario or not but I would at least consider it...and I'm quite sure that if your business grows you will have several tables that needs partitioning and you might end up with thousands of tables in your database, many of which with only a few thousand rows maybe.I'm not saying it's a definite nono and partitioning is one of the very few reasons I could be convinced to agree that adding tables dynamically could be an idea, but you have to consider the consequences...- Lumbago |
|
|
|
|
|
|
Data partitioning