In SQL Server, you can use the sp_tables system stored procedure to get a list of tables and views in the current environment.
You can return all tables and views, or you can narrow it down to a particular owner, type, pattern, or even a specific table or view.
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.
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.
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.
In SQL Server, the sp_sproc_columns system stored procedure enables you to get column information for a given stored procedure or user-defined function in the current system.
It’s equivalent to SQLProcedureColumns in ODBC.
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.
The “Server is not configured for DATA ACCESS” error in SQL Server is a common error when trying to run a distributed query against a server that has its data access setting disabled.
The error will have the name of the server that you’re trying to access. For example, if your server name is SQL01, the error will read something like this:
Msg 7411, Level 16, State 1, Line 1 Server 'SQL01' is not configured for DATA ACCESS.
“Data access” is a setting that enables and disables a linked server for distributed query access.
A common cause of this error is when you try to run OPENQUERY() against the local server. If you really want to run OPENQUERY() against the server, you’ll need to ensure data access is enabled for that server – even if it’s your local server.
In SQL Server, there’s a “data access” setting that enables and disables a linked server for distributed query access. If you ever get the “Server is not configured for DATA ACCESS” error, it’s probably because you’re trying to run a distributed query against a linked server that isn’t configured for data access. This can also happen when you try to run OPENQUERY() against your local server.
You can use sp_serveroption to enable or disable data access on a given server. However, you might want to check the existing settings before you start changing them. The following examples show how to do that.
SQL Server has a “data access” configuration option that enables and disables a linked server for distributed query access.
If you ever get a “Server is not configured for DATA ACCESS” error, you’ll probably need to enable data access for the linked server you’re trying to run the distributed query against. Conversely, there may also be times where you need to disable data access.
To enable or disable data access, use the sp_serveroption system stored procedure. Execute it against the server that you intend to run distributed queries from. The following example demonstrates how to do this.
In SQL Server the sp_server_info system stored procedure returns a list of attribute names and matching values for SQL Server, the database gateway, or the underlying data source. It returns a subset of the information provided by SQLGetInfo in ODBC.
Basically, it allows you to see information about SQL Server.