If you have two objects with the same name, it is almost certain that one is in a different schema. So to differentiate, you need to include the schema name in the call to
sp_help as well.
EXEC sys.sp_help N'dbo.tablename';
EXEC sys.sp_help N'otherschema.udtname';
You should always be using schema-qualified object names anyway:
And in the case where you managed to create a table and a UDDT with the same name under the same schema:
- I believe this is a bug in the engine. It should not allow me to create a data type and an object with the same name under the same schema. And I don't know why you would want to do that, even though it is allowed.
sp_help is certainly not prepared for that (and I don't see any chance for their investment in fixing it, especially if 1. is correct). It will return the information for the table, but not the type. The documentation mentions nothing of this, of course - so at the very least there is information missing in the docs, and perhaps you could also consider this a feature gap (
sp_help could take type as an argument, but again, don't hold your breath).
So your options in the meantime are:
- Rename the type.
- Don't expect to use
sp_help to get information about the type.
- Write your own version of
sp_help has the following basic logic, which you could change to include both:
-- @objname must be either sysobjects or systypes: first look in sysobjects
-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
-- IF NOT IN SYSTYPES, GIVE UP
Or you could add a parameter that lets you decide which object class is more important.
Of course you need to fix any line that uses
TypePropertyEx(), an undocumented system function, to do this instead:
--'Prec' = Convert(int,TypePropertyEx(user_type_id, 'precision')),
[Prec] = [precision],
--'Scale' = Convert(int,TypePropertyEx(user_type_id, 'scale')),
[Scale] = [scale],
Why they use
TypePropertyEx() here, I don't have a clue.
But, in all honesty, the best solution is to rename your type. Who knows what other conflicts you'll come across.