Tag: t-sql

Find Dependencies in SQL Server: sql_expression_dependencies

Posted on by Ian

In SQL Server, you can use the sys.sql_expression_dependencies system catalog view to return all dependencies on a user-defined entity in the current database. This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules.

You can use this view to:

  • Return entities that depend on a given entity
  • Return entities on which a given entity depends

So for example, you could use it to return all objects that reference a specific table. You could also use it to return all objects that a specific stored procedure references within its code.

Continue reading →

Example of SQL Server’s sys.dm_sql_referenced_entities() Returning an Entity that References a Linked Server

Posted on by Ian

One of the things about the sys.dm_sql_referenced_entities() system dynamic management function is that you can use it on cross-database and cross-server entities.

This means you can find referenced entities that are in a different database and even on a different server.

This article provides an example of sys.dm_sql_referenced_entities() returning a stored procedure that queries a database on a linked server.

Continue reading →

Quickest Way to List All Databases in SQL Server using T-SQL

Posted on by Ian

If you’re using SQL Server via a command line interface, you probably don’t see a persistent list of databases like you normally would while using a GUI (such as SSMS or Azure Data Studio).

In such cases, you can use the sp_databases system stored procedure to return a list of databases. This stored procedure specifically lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway.

This is probably the quickest and easiest way of listing all databases using T-SQL. You can simply type sp_databases for a list of databases, or to be more explicit, EXEC sp_databases.

Continue reading →

Get Column Info for a Table or View in SQL Server (T-SQL: sp_columns)

Posted on by Ian

In SQL Server you can use the sp_columns system stored procedure to return column information for the specified objects that can be queried in the current environment. Such a objects include tables, views, or other objects that have columns such as table-valued functions.

You can get information for a specific column, or you can specify all columns from a given table, view, etc.

Continue reading →

Get Column Privileges for a Table in SQL Server using T-SQL: sp_column_privileges

Posted on by Ian

In SQL Server you can use the sp_column_privileges system stored procedure to return column privilege information for a given table in the current environment.

Provide the table name as an argument when executing the stored procedure, and the column privileges will be returned for that table. You can also supply the table owner, table qualifier, and/or the column name if required.

Continue reading →

How to Find the Optimal Unique Identifier in a Table in SQL Server: sp_special_columns

Posted on by Ian

In SQL Server, you can use the sp_special_columns system stored procedure to identify a unique identifier for the table. Specifically, it returns the optimal set of columns that uniquely identify a row in the table. It also returns columns automatically updated when any value in the row is updated by a transaction.

sp_special_columns is equivalent to SQLSpecialColumns in ODBC.

If there are no columns that can uniquely identify the table, the result set is empty.

Continue reading →