OBJECT_DEFINITION Function Not Supported

Dave Pendleton

SQL Prompt Version 9.5.23.12091 does not recognize the OBJECT_DEFINITION function. If this function appears in a script, most SQL Prompt functionality is broken. It is also not formatted correctly. 

squigley

Hello Dave,

Can you send us a screenshot of the issue as well as more details on how you run into it?

We have tried replicating the issue using the following script, but we weren't successful in doing so:

GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_columns')) AS [Object Definition];
GOUSE AdventureWorks2012;

Thanks!

Dave Pendleton

The function call is part of a larger WHERE clause and looks like this:

OR (
   [referenced].[referenced_minor_name] IS NULL
   AND [OBJECT_DEFINITION](OBJECT_ID(referencing_schema_name + '.' + referencing_entity_name)) LIKE '%' + @ColumnName + '%'
   )</code>OR (
   [referenced].[referenced_minor_name] IS NULL
   AND OBJECT_DEFINITION(OBJECT_ID(referencing_schema_name + '.' + referencing_entity_name)) LIKE '%' + @ColumnName + '%'
   )
</pre><div>Because of this, code suggestions, etc. do not work in this file until I comment it out, or "Format Code" which turns it into this:<br><pre class="CodeBlock"><code>

Note the brackets around the function name; this is obviously not correct either. The other erratic behavior I can't demonstrate in a forum post.

squigley

Hello Dave,

Thanks for those additional details. I did some further testing on my end and I was only able to encounter issues, specifically with the square brackets, when running on an Azure SQL Database.

Can you confirm if that's where you're running this on your end? It would make sense if you're running into issues then as we don't currently support SQL Prompt with Azure.

Dave Pendleton

OBJECT_DEFINITION is not an Azure-specific function.

squigley

Hello Dave,
Can you confirm if you're running Azure SQL Database and if so, are you running into this issue on said Azure instance? Or is this on a standalone SQL Server Instance?

Dave Pendleton

Both.

squigley

Hello Dave,

Can you let us know what version of SQL Server that you're encountering this issue under? We were only able to encounter this issue on Azure SQL Database from our end and couldn't replicate it with our initial tests on our standalone SQL Server instances.