System Stored Procedures (Transact-SQL) 02/21/2016; 4 minutes to read +1; In this article.
Applies to: SQL Server 2016 (13.
x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (SQL DW) In SQL Server 2019 (15.
x), many administrative and informational activities can be performed by using system stored procedures.
, Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData Definition: sys.
sp_tables_rowset (nvarchar @table_name, nvarchar @table_schema, nvarchar @table_type) MetaData:, System Tables (Transact-SQL) 03/15/2017; 2 minutes to read +2; In this article.
Applies to: SQL Server (all supported versions) The topics in this section describe the system tables in SQL Server.
, While adding linked server from SQL Management, you are not given option to set default database.
So use something like below.
EXEC sp_addlinkedserver @server=’name for referring locally’, — here you can specify the name of the linked server @srvproduct=”, @provider=’sqlncli’, — using SQL Server native client @datasrc=’AzureMachineName.
net’, — add here your server name.
, I have an ASP application – that for just a couple SQL Statements – causes the sp_indexes_rowset and sp_primary_keys_rowset to get invoked under a separate connection.
, create procedure sys.
sp_tables_rowset ( @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null ) as select TABLE_CATALOG = s_tr.
TABLE_CATALOG, TABLE_SCHEMA = s_tr.
TABLE_SCHEMA, TABLE_NAME = s_tr.
TABLE_NAME, TABLE_TYPE = s_tr.
TABLE_TYPE, TABLE_GUID = s_tr.
TABLE_GUID, DESCRIPTION = s_tr.
DESCRIPTION, #, Catalog Information.
The power of linked servers is the ability to work with heterogeneous data sources via a single SQL Server instance.
The catalog views will not help us when the provided linked server talks to an Microsoft Access database.
, They are “system” Sprocs, you can see what they do using: master.
sp_helptext ‘sp_columns_rowset’ Dunno why your application calls them repeatedly, but it might be the ADO layer that is doing the calling, rather than the application.