Início / dba / Perguntas / 151359
Accepted
webdad3
Asked: 2016-10-04 13:49:48 +0800 CST

Puxe dados de sys.columns em um banco de dados diferente

  • 772

Estou executando o seguinte em uma instância no servidor:

SELECT c.*
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@tablename);

Tudo funciona muito bem.

Gostaria de executar a mesma instrução, mas apontá-la para outro banco de dados no servidor.

databaseName.dbo.tablename

Eu tentei o seguinte (e outras variações), mas não funcionou.

SELECT c.*
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('databaseName.dbo.tablename');

Existe uma maneira de obter as informações da coluna em um banco de dados de outro?

Código:

SELECT @SQL = @SQL + 'INSERT INTO myTable 
  (ColumnName,ColumnValue,SID,SName,RID,RName)
  VALUES (''' + QUOTENAME(c.name) + ''',NULL,0,NULL,0,NULL);'
            FROM sys.columns c
            WHERE c.object_id = OBJECT_ID(@tablename);

EXEC(@SQL);

Existe uma maneira de fazer isso dinamicamente? Se eu tivesse um @DBNameparâmetro que estivesse passando.

sql-server

4 respostas

  1. Best Answer

    Adicione o banco de dados que você deseja consultar na frente do sys.columns.

    SELECT c.*
FROM <other database name>.sys.columns c
WHERE c.object_id = OBJECT_ID(@tablename);
    • 7

  2. Você pode fazer isso dinamicamente desta maneira:

    -- parameters to the procedure:
DECLARE @dbname sysname = N'master', @tablename sysname = N'spt_values';


DECLARE @sql nvarchar(max),
  @exec nvarchar(max) = QUOTENAME(@dbname) + N'.sys.sp_executesql';

SET @sql = N'--insert SourceDatabase.dbo.myTable(...)
  SELECT QUOTENAME(c.name),NULL,0,NULL,0,NULL 
  FROM sys.columns AS c -- these will be in @dbname
  INNER JOIN sys.all_objects AS t
  ON c.[object_id] = t.[object_id]
  WHERE t.[schema_id] = 1    -- seems you assume everything will be dbo.
  AND t.name = @tablename;'; -- don't need to protect table name

-- magic sauce: this turns into EXEC dbname.sys.sp_executesql @sql...:
EXEC @exec @sql, N'@tablename sysname', @tablename;

    Isso é um pouco mais seguro, IMHO, do que concatenar @dbnamena declaração.

    Se você precisar de suporte para mais de dbo(novamente, fiz uma suposição com base em todas as consultas da pergunta), é fácil adicionar:

    -- parameters:
DECLARE @dbname     sysname = N'master', 
        @schemaname sysname = N'dbo', 
        @tablename  sysname = N'spt_values';


DECLARE @sql nvarchar(max),
  @exec nvarchar(max) = QUOTENAME(@dbname) + N'.sys.sp_executesql';

SET @sql = N'--insert SourceDatabase.dbo.myTable(...)
  SELECT QUOTENAME(c.name),NULL,0,NULL,0,NULL 
  FROM sys.columns AS c -- these will be in @dbname
  INNER JOIN sys.all_objects AS t
  ON c.[object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = @schemaname
  AND t.name = @tablename;';

-- magic sauce: this turns into EXEC dbname.sys.sp_executesql @sql...:
EXEC @exec @sql, N'@tablename sysname, @schemaname sysname', @tablename, @schemaname;
    • 5

  3. Você também pode juntar tabelas do sistema no banco de dados remoto

    DECLARE @DBName sysname;
DECLARE @tablename sysname;
SELECT  @DBName = N'<yourDB>' ,
        @tablename = N'<yourTable>'

DECLARE @SQL NVARCHAR(MAX);
SELECT  @SQL = 'INSERT INTO myTable (ColumnName,ColumnValue,SID,SName,RID,RName)
SELECT QUOTENAME(c.name),NULL,0,NULL,0,NULL
FROM    ' + @DBName + '.sys.tables t
        JOIN ' + @DBName + '.sys.columns c ON t.object_id = c.object_id
WHERE   t.name = ''' + @tablename + '''';

EXEC (@SQL);

    Nesse caso, se você tivesse a mesma tabela em vários esquemas, obteria resultados incorretos. Você pode corrigir isso também juntando-se à tabela sys.schemas com o esquema do nome da tabela.

    *SQL atualizado para ser dinâmico conforme solicitado na pergunta atualizada

    • 0

  4. A solução @tpet requer o nome completo da tabela, ou seja, com o banco de dados e o prefixo do esquema:

    SELECT c.*
FROM OtherDatabase.sys.columns c
WHERE c.object_id = OBJECT_ID('OtherDatabase.OtherSchema.OtherTable')

    A consulta SQL alternativa abaixo funcionará apenas com o nome da tabela:

    SELECT c.*
FROM OtherDatabase.sys.columns c
WHERE OBJECT_NAME(c.object_id, DB_ID('OtherDatabase')) = @TableName;
    • 0

relate perguntas