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.
Category: DBMS
Database Management Systems
How to make RAND() Deterministic in SQL Server
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 readingHow to Encrypt a View in SQL Server
In 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.
Difference Between sys.columns, sys.system_columns, & sys.all_columns in SQL Server
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.
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 (
##)