This article presents four ways to return a list of user-defined views in a SQL Server database.
If you want to see only system views, or both user-defined and system views, see Difference Between sys.views, sys.system_views, & sys.all_views in SQL Server.
If you prefer to administer your SQL Server databases by running T-SQL queries, instead of using the GUI, here are two options for listing all triggers in the current database.
This article presents two ways to return a list of user-defined functions in a SQL Server database.
In SQL Server, you can use the SQL_VARIANT_PROPERTY() function to return base data type information from a sql_variant value.
The function accepts two arguments: the sql_variant value, and the property for which information is to be provided.
In SQL Server, if you try to drop an object that doesn’t exist, you’ll get an error. If you don’t want to get an error, you’ll need to add some extra code to check for the existence of the object.
Prior to SQL Server 2016, you needed to add an IF statement that queried the relevant system objects to find out whether or not the object existed.
From SQL Server 2016, you can now use a new, cleaner method to check if an object exists. We’ll call it DROP IF EXISTS (otherwise known as “DIE”).
When using SQL Server, you can use the sys.dm_os_host_info system dynamic management view to return the operating system that SQL Server is running on.
This view includes a column called host_platform which contains the type of operating system: Windows or Linux
In SQL Server environments, two of the many string functions at our disposal are LEFT() and SUBSTRING().
These functions do a similar thing, but there are differences. This article looks at some of the main differences between these functions.
You may be familiar with the SET TEXTSIZE statement in SQL Server, that enables you to limit the amount of data returned in a SELECT query.
Perhaps you’re finding that its results are exactly the same as LEFT() when running a specific query. Which begs the question: Is there a difference between these two options?
Have you used SET TEXTSIZE to limit the data returned by a query, but found that it just doesn’t seem to work? You’re finding that your SELECT queries continue to return the full amount of text, regardless of the TEXTSIZE value?
Read on. You could be making this one obvious mistake.
When I first encountered the TEXTSIZE option, I decided to run a quick test. But it didn’t work. I tried several times with various TEXTSIZE values, but nothing seemed to work.
“Why isn’t it working?” I wondered.
But then it clicked. I re-read the documentation and realised that I wasn’t using the correct string size for my data types.
In SQL Server, the SET TEXTSIZE statement specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement.
Microsoft advises that text, ntext, and image will be removed in a future version of SQL Server, so you should avoid using these data types in new development work, and plan to modify applications that currently use them to use varchar(max), nvarchar(max), or varbinary(max) instead.