In SQL Server, you can use the sp_helptrigger stored procedure to return the type or types of DML triggers defined on the specified table for the current database.
This stored procedure only works on DML triggers (not DDL triggers).
Tag: mssql
Difference Between sys.objects, sys.system_objects, and sys.all_objects in SQL Server
If you’ve used T-SQL for a while, it’s likely you’ve encountered the sys.objects system catalog view, which returns user-defined, schema-scoped objects in the database.
But there are two other similar views that you may or may not be aware of; sys.system_objects and sys.all_objects.
2 Ways to See if Deprecated Features are Still Being Used in a SQL Server Instance
It’s amazing how quickly some features can become deprecated in the world of software.
This article presents two methods to see whether deprecated features are being used in a SQL Server instance.
Create a Temporary Table in SQL Server
In SQL Server, temporary tables are created using the same CREATE TABLE syntax as regular tables. The difference is that temporary tables’ names are prefixed with either one or two number signs (#), depending on whether it’s a local temporary table or global temporary table:
- Local temporary tables are prefixed with a single number sign (
#) - Global temporary tables are prefixed with a double number sign (
##)
Introduction to Temporary Tables in SQL Server
In SQL Server, a temporary table is a certain kind of table that exists until goes out of scope (unless it’s explicitly dropped).
This is different to a regular (persistent) table, where the regular table exists permanently in your database until you explicitly drop it.
What is @@SERVICENAME in SQL Server?
In SQL Server, the @@SERVICENAME configuration function returns the name of the registry key under which SQL Server is running.
No argument is required. You can simply use it in a SELECT statement to return the registry key’s name.
Note that SQL Server runs as a service named MSSQLServer. The @@SERVICENAME function returns MSSQLSERVER if the current instance is the default instance. It returns the instance name if the current instance is a named instance.
3 Ways to List All Stored Procedures in a SQL Server Database
This article presents two ways to return a list of stored procedures in a SQL Server database.
How to Encrypt a Stored Procedure in SQL Server
In SQL Server, you can encrypt a stored procedure at the time you create it, or you can alter it later to include encryption.
To create a stored procedure with T-SQL, you use the CREATE PROCEDURE syntax. To encrypt it, you add the WITH ENCRYPTION argument.
You can also use the same argument to encrypt an existing procedure when using ALTER PROCEDURE.
“Internal connection fatal error” When Executing a Natively Compiled Stored Procedure in SQL Server 2019 (Known Bug)
If you’re getting the following error (or similar error) while trying to execute a natively compiled stored procedure in SQL Server 2019, read on.
Continue readingQuery failed: Internal connection fatal error.
How to Create a Schema Bound Stored Procedure in SQL Server
If you’ve ever created a schema bound UDF, you’ll know that schema binding it is just a matter of adding WITH SCHEMABINDING to your definition. The same applies when you create a schema bound view.
Stored procedures are a bit different.
Only natively compiled stored procedures can be schema bound. In fact, natively compiled stored procedures must be schema bound. You can’t create a natively compiled stored procedure without schema binding it.
But if you try to schema bind a regular (non-natively compiled) procedure, you’ll get an error.
Continue reading