Sql-server – sp_help with different object types with the same name

sql serversql-server-2008-r2t-sql

I have a table with the same name as a user-defined data type (UDDT), and when I use sp_help, only the table definition is shown.

Is there a way to use sp_help or an equivalent to show the definition for the UDDT instead?

sp_help only appears to have an @ObjName parameter, with no way to specify I actually want information about a type.

This script demonstrates the ability to create a table and a user-defined data type with the same name and under the same schema:

CREATE TYPE [dbo].[Address] FROM [nvarchar](40) NULL;
GO

CREATE TABLE [dbo].[Address]
(
    [AddressID] Int IDENTITY(1,1) NOT NULL
);

Best Answer

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:

  1. 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.
  2. 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:

  1. Rename the type.
  2. Don't expect to use sp_help to get information about the type.
  3. Write your own version of sp_help.

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],
....
FROM sys.types
...

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.

Related Question