1

The sp_droptype procedure can be used to drop a User Defined Type.

So I have a number of such sp_droptype calls in my "clean" script which I invoke using sqsh -i. However it is not possible to conditionally invoke sp_droptype only if the type does in fact exist or to instruct it to be silent if the type doesn't exist.

Is there a way to test if a type exists or not and only invoke sp_droptype if it actually exists? This would result in cleaner output.

1 Answer 1

2

This works the same as in SQL Server; use the following style of construct:

IF OBJECT_ID('dbo.MyObject') IS NOT NULL
BEGIN
    EXEC sp_droptype 'dbo.MyObject';
END

OBJECT_ID(...) returns an integer representing the object_id from sysobjects for every object stored in the current database. It will return NULL if the object does not exist.

Another way of testing for object existence:

IF EXISTS (SELECT 1 FROM systypes st WHERE st.name = 'MyType')
BEGIN
   EXEC sp_droptype 'MyType';
END

I don't have the ability to create a type in ASE at the current time, so I cannot say for certain if OBJECT_ID() will work.

2
  • this returns NULL for all my user defined types. Maybe it's the systypes table? I can see my types in that table but there are more types in there (e.g. bit, char, date). How do I tell them apart? Jan 20, 2017 at 22:57
  • I added another way to see if the type exists to my answer. Unfortunately, I'm not at my ASE instance at the present time.
    – Hannah Vernon
    Jan 20, 2017 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.