I used your approach two years ago for a similar problem, it works fairly well, except a bit pain with linq to sql (or entity framework), plus WinForm:
We created a few updatable views for each type of entity (or customer as per your sample) but linq to sql could not cope well with updatable views, or probably it's not the case now.
Now we used a different approach:
In the main table, we will have a column named Descriminator (something like UserType),
then for each type of entity, we will have its delegated table (extended from the main table), which uses same PrimaryKey as the main table - so of course, a foreign constraint refer back to the main table (i.e. Primary Key and foreign key on the same field), and the delegated table will have the specific columns related to that user type.
it works well too, and with this, we can take the advantage of entityframework's inheritance feature.
that's just my experience, so my suggestion is to write some small test apps to test out your approach(es) and evaluate. then get the best that has good performance and also suits your framework for UI.
sp_rename upates the name in sys.objects but it does not update the definition or objects referencing it.
Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name. (MSDN)
When you run this query:
Create Procedure Test
as
begin
set nocount on
end
Go
Exec sp_rename 'Test', 'Tests'
GO
Select * From sys.sql_modules
sp_helptext 'Tests'
The definition
column in sql_modules
still contains Test
:
object_id definition ...
599673184 Create Procedure Test as begin set nocount on Select id From T1 end
- Rename a Table (or an object)
When an object is rename, any object using it and referencing the old name will fail.
Here are a View and a Table beining renamed:
Create Table T1(id int)
Go
Create View V1 AS Select id From dbo.T1
Go
Exec sp_rename 'T1', 'T2'
Again if you look at the definition, both the procedure and the view reference T1 (which does not exist anymore):
object_id definition
759673754 Create Procedure Test as begin set nocount on Select id From T1 end
791673868 Create View V1 AS Select id From dbo.T1
And both will fail with this error when you Select from it or Execute it:
Invalid object name 'dbo.T1'.
Could not use view or function 'V1' because of binding errors.
You get the same error if you refresh the view:
sp_refreshview 'v1'
All objects with references to the old name will break.
Before renaming an object (table, procedure, function, ...), you can look at its dependencies using sys.sql_expression_dependencies
(MSDN) (deprecated: sys.sql_dependencies
MSDN, sys.sysdepends
(MSDN)
Best Answer
sys.sql_expression_dependencies
should give you what you want if you don't want to search through the definition of every single object (especially when references may or may not have schema prefixes).I think you can get what you want with the following query: