Three of the system catalog views in SQL Server include sys.columns, sys.system_columns, and sys.all_columns.
These three catalog views each provide metadata about columns in the database, but there’s a difference between them.
Tag: t-sql
Remove SCHEMABINDING from a View in SQL Server
The purpose of schema binding a view is to ensure that the base tables referenced in the view cannot be modified in a way that would affect the view definition.
This is normally a good thing. After all, you don’t want someone coming along and dropping a table that your view depends on, do you?
But what if you need to make changes to one or more tables referenced by your view?
Continue readingCreate a Schema Bound View in SQL Server
It’s usually a good idea to schema bind your views in SQL Server.
Schema binding your view will ensure that the underlying tables can’t be changed in a way that would affect the view. Without schema binding, the underlying tables or other objects could be modified or even deleted. If that happens, the view will no longer work as expected.
Continue readingReturn a DML Trigger’s Type on a Table in SQL Server
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).
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.