If you need to run the SERVERPROPERTY() function against a linked server in SQL Server, you can use a pass-through query. To do this, pass the SERVERPROPERTY() function as the second argument to the OPENQUERY() function (the first argument is the linked server name).
Tag: linked servers
List All Databases from a Linked Server in SQL Server (T-SQL Examples)
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.
Return Primary Keys from a Linked Server in SQL Server (T-SQL Examples)
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.
Return Table Privileges from a Linked Server in SQL Server (T-SQL Examples)
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.
Return Column Privileges from a Linked Server in SQL Server (T-SQL Examples)
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.
Return Column Information from a Linked Server in SQL Server (T-SQL Examples)
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.
Return a List of Tables from a Linked Server in SQL Server (T-SQL Examples)
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).
How Logins Work on Linked Servers (T-SQL Examples)
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.
Add a Linked Server Login in SQL Server (T-SQL Example)
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.
Create a Linked Server in SQL Server (T-SQL Example)
This article provides an example of creating a linked server in SQL Server using Transact-SQL. Using T-SQL enables you to create the linked server without relying on a graphical user interface (such as SSMS).