CODEWITHSUNDEEP
sql-serversql-server-2014
Jonathan Stanton
Jonathan Stanton

Reputation: 2630

Issues with sp_HelpText stripping out dbo schema in response

When I call the following

EXEC sp_HelpText 'dbo.NameOfTrigger'

I get the following returned

CREATE TRIGGER NameOfTrigger ON [dbo].[NameOfTable] FOR INSERT, UPDATE, DELETE

However when I call the same type of call but on another schema

EXEC sp_HelpText 'AnotherSchema.NameOfTrigger'

I get the result

CREATE TRIGGER [AnotherSchema].[NameOfTrigger] ON [AnotherSchema].[NameOfTable]

Is there a way of forcing SQL Server (MSSQL 2014) to include the [dbo] added to the NameOfTrigger e.g.

CREATE TRIGGER [dbo].[NameOfTrigger] ON [dbo].[NameOfTable] FOR INSERT, UPDATE, DELETE

We need this for some code in Entity Framework to handle table renames in a custom migration SQL generations.

Upvotes: 1

Views: 345

Answers (1)

squillman
squillman

Reputation: 13641

I was trying some things in my dev environment to see if I could replicate your issue. It seems your trigger was originally created without specifying the dbo schema in the CREATE TRIGGER statement.

This CREATE TRIGGER statement results in the dbo schema being displayed in the sp_helptext results:

CREATE TRIGGER dbo.NameOfTrigger ON ...

However, this one doesn't:

CREATE TRIGGER NameOfTrigger ON ...

(The second statement still defaults the schema to dbo.)

Example:

sp_helptext example

So I would say try dropping and recreating your trigger, but this time use the dbo schema in the CREATE TRIGGER statement. Then you should be able to feed to EF.

Chalk another one up to "always specify the schema"...

Upvotes: 1

Related Questions