If you’re looking for a catalog view to return parameter information in SQL Server, you have a choice. In particular, you can get parameter information from sys.parameters, sys.system_parameters, and sys.all_parameters.
However, you will probably only want to use one of these views, as there are differences between them.
Here’s some T-SQL code you can use to get a list of all CHECK and foreign key constraints in a SQL Server database.
The RAND() function in SQL Server returns a pseudo-random float value from 0 through 1, exclusive.
This function can be deterministic or nondeterministic, depending on how it’s invoked.
Continue readingIn SQL Server, you can encrypt a view at the time you first create it, or you can alter it later to include encryption.
To create a view with T-SQL, you use the CREATE VIEW syntax. To encrypt it, you add the WITH ENCRYPTION argument.
You can also use the same argument to encrypt an existing view when using ALTER VIEW.
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.
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 readingIt’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 readingIn 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).
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.
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.