Найти столбцы Identity из другой базы данных

Object_ID работает только в текущей базе данных, если вы не используете имя из трех частей, но эта форма сложна в использовании. Кроме того, ColumnProperty работает только в текущей базе данных.

select o.name + '.' + c.name, o.name
from test1.sys.columns c
join test1.sys.objects o on c.object_id = o.object_id
join test1.sys.schemas s on s.schema_id = o.schema_id
where s.name = 'dbo'
  and o.is_ms_shipped = 0 and o.type = 'U'
  and c.is_identity = 1
order by o.name

Нет возможности получить информацию с помощью COLUMNPROPERTY из другой базы данных. Но есть обходной путь:

DECLARE @DatabaseName VARCHAR(MAX)
DECLARE @TableName VARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)

SET @DatabaseName = 'MyDatabase'
SET @TableName = 'MyTable'

SET @SQL = '
SELECT 
    C.TABLE_NAME,
    C.COLUMN_NAME,
    S.IS_IDENTITY
FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.COLUMNS AS C
LEFT JOIN ' + @DatabaseName + '.SYS.COLUMNS AS S ON OBJECT_ID(''' + @DatabaseName + '.dbo.' + @TableName + ''') = S.OBJECT_ID AND C.COLUMN_NAME = S.NAME
WHERE S.IS_IDENTITY = 1'

EXEC(@SQL)

Это сработало для меня, используя определенную базу данных:

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    , t.name AS table_name
    , c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO

В этом примере я создал хранимую процедуру в «Database1», которая использует динамический SQL для извлечения информации о столбцах из таблицы в «Database2» (используя системное представление [INFORMATION_SCHEMA].[COLUMNS], находящееся в «Database2»):

ALTER PROCEDURE [Database1].[Schema1].[ColumnNames] @Database2 nvarchar(128), @Schema2 nvarchar(128), @Table2 nvarchar(128)
AS

BEGIN
    DECLARE @Sql nvarchar(1000)

    SET @Sql = 'SELECT [COLUMN_NAME], [ORDINAL_POSITION] FROM [' + @Database2 + '].[INFORMATION_SCHEMA].[COLUMNS]
                WHERE [TABLE_SCHEMA] = ''' + @Schema2 + ''' AND [TABLE_NAME] = ''' + @Table2 + ''''
    EXEC(@Sql)
END 

Я использую SQL Server 2019 и столкнулся с той же проблемой. Я не уверен, что это исправление будет работать для более старых версий, но в каждой БД есть представление с именем Ваше-БД-Имя.sys.identity_columns. Если вы выберете из этого представления, вы увидите список столбцов идентификаторов, которые вы определили в этой БД.

На основе этой информации вы сможете написать соединение, соединяющее Имя вашей БД.Информационная_схема.столбцы, как показано ниже:

SELECT *
FROM YourDBName.Information_Schema.columns col
LEFT OUTER JOIN YourDBName.sys.identity_columns idc
ON idc.name = col.COLUMN_NAME AND idc.object_id = object_id('YourDBName..YourTableName')< br> ГДЕ col.TABLE_NAME = 'YourTableName' AND col.table_catalog = 'YourDBName';

Представление YourDbName.sys.identity_columns содержит следующие полезные поля:

• object_id (используется для обратного присоединения к рассматриваемой таблице, если у вас есть несколько таблиц с одинаковым именем поля идентификации)
• name (название поля Identity)
• column_id (порядок столбца в таблице)
• is_identity (указывает, является ли это полем идентификации)
• seed_value (начальное значение поля идентификации)
• increment_value (насколько увеличивается поле идентификации при каждой вставке)