Início / dba / Perguntas / 221441
Accepted
James
Asked: 2018-11-01 05:29:33 +0800 CST

Consulta para obter todas as colunas em uma visualização

  • 772

Eu tenho esta pequena cantiga para concatenar todas as colunas em uma tabela, para escrever scripts rapidamente:

    create function [dbo].[get_columns] (@table_name varchar(255))
    returns varchar(max)

    as
    begin
    declare @name varchar(255),
    @object_id varchar(255),
    @string varchar(max)


    set @object_id = (select object_id from sys.objects with (nolock) where name = @table_name and type = 'u')

    set @string = (select stuff( (
    select ', ' + name   from sys.columns with (nolock)
    where object_id = @object_id
    and name != 'row_timestamp'
    for xml path ('')),1,2,''))

    return @string
    end

Pergunta: como você reescreveria isso para obter as colunas de uma visão?

sql-server sql-server-2008

2 respostas

  1. Best Answer

    Por que ter uma função para tabelas e uma função separada para visualizações? Você pode lidar com ambos apenas removendo o filtro restritivo type = 'u'(e, na verdade, você não precisa sys.objects). Aqui está uma reescrita com algumas outras pequenas melhorias:

    CREATE FUNCTION dbo.get_columns
(
  @object_name nvarchar(513) 
  -- needs to support Unicode!
  -- should pass schema.object
)
RETURNS nvarchar(max) -- can contain Unicode!
AS
BEGIN -- we don't need any variables
  RETURN 
  (
    SELECT STUFF((SELECT N', ' + name 
      FROM sys.columns WITH (NOLOCK)
      WHERE [object_id] = OBJECT_ID(@object_name)
      AND name <> N'row_timestamp'
      FOR XML PATH(''), TYPE).value(N'.[1]',N'nvarchar(max)'),1,2,'')
  );
END
    • 2

  2. Tente envolver isso em funções com valor de tabela inline

    CREATE FUNCTION dbo.get_all_columns
    ( @tablename sysname,@schemaname sysname ) 
RETURNS TABLE
RETURN 
SELECT TABLE_NAME,STUFF((SELECT N', ' + p2.COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS p2
   WHERE p2.TABLE_NAME = p.TABLE_NAME 
   ORDER BY p2.ORDINAL_POSITION
   FOR XML PATH(N'')), 1, 2, N'') AS columns
FROM INFORMATION_SCHEMA.COLUMNS p
WHERE TABLE_NAME = @tablename AND TABLE_SCHEMA=@schemaname
GROUP BY p.TABLE_NAME
GO

    em seguida, junte-se ao esquema de informações da tabela

    SELECT            a.*,
                  d.*
FROM              INFORMATION_SCHEMA.TABLES                         a
      CROSS APPLY dbo.get_all_columns(a.TABLE_NAME, a.TABLE_SCHEMA) d;

    para obter as colunas em uma visão como você disse, altere a consulta o esquema de informaçõesVIEW_COLUMN_USAGE

    CREATE FUNCTION dbo.get_all_columns_view
    ( @viewname sysname,@schemaname sysname ) 
RETURNS TABLE
RETURN 
SELECT VIEW_NAME,STUFF((SELECT N', ' + p2.COLUMN_NAME 
  FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE p2
   WHERE p2.VIEW_NAME = p.VIEW_NAME
   ORDER BY p2.VIEW_NAME
   FOR XML PATH(N'')), 1, 2, N'') AS columns
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE p
WHERE p.VIEW_NAME = @viewname AND p.VIEW_SCHEMA=@schemaname
GROUP BY p.VIEW_NAME
GO
    • 1

relate perguntas