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.
In SQL Server you can use the sp_catalogs system stored procedure to return a full list of databases in a linked server.
Strictly speaking, this stored procedure returns the list of catalogs in the specified linked server. This is equivalent to databases in SQL Server.
In SQL Server you can use the sp_primarykeys system stored procedure to return the primary key columns from a specified linked server. It returns one row per key column, for the specified remote table.
The simplest way to execute this stored procedure is to pass the name of the linked server. Doing that will return all primary keys from the default database on the specified linked server.
You also have the option of specifying a different database and/or a specific table schema.
In SQL Server you can use the sp_table_privileges_ex system stored procedure to return privilege information about a specified table from a specified linked server.
You can specify an individual table, or you can specify all tables from a given database or table schema. You can also use wildcard characters to specify the table/s. However, you can also specify whether the wildcard characters should be interpreted as wildcard characters.
In SQL Server you can use the sp_column_privileges_ex system stored procedure to return column privileges for the columns from a specified linked server.
You can specify an individual column, or you can specify all columns from a given database, table, etc.
In SQL Server you can use the sp_columns_ex system stored procedure to return column information about the columns from a specified linked server.
You can specify an individual column, or you can specify all columns from a given database, table, etc.
In SQL Server you can use the sp_tables_ex system stored procedure to return table information about the tables from a specified linked server.
The simplest way to execute this stored procedure is to pass the name of the linked server. Doing that will return all tables from the default database on the specified linked server, including system tables and views. This could be a big list.
You also have the option of specifying a different database and/or a specific table schema. You can also filter the results based on the table type (e.g. table, view, system table, etc).
In this article, I configure a linked server in SQL Server so that only a single local login can access it. To do this, I delete any mappings that are automatically created when I create the linked server, before adding a login mapping for the single local login.
When configuring a linked server in SQL Server, setting up the logins can sometimes be confusing. In this article, I aim to provide a high-level overview of how SQL Server maps local logins to remote logins on the linked server.
In this article, I create a login for a linked server in SQL Server. In this case I use Transact-SQL (as opposed to using a GUI such as SSMS).
To create a login for linked server, use the sp_addlinkedsrvlogin system stored procedure.