主页 / dba / 问题 / 221441
Accepted
James
Asked: 2018-11-01 05:29:33 +0800 CST

查询以获取视图中的所有列

  • 772

我有这个小曲子来连接表格中的所有列，以便快速编写脚本：

    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

问题：您将如何重写它以获得视图的列？

sql-server sql-server-2008

2 个回答

  1. Best Answer

    为什么有一个表函数和一个单独的视图函数？您可以通过删除限制性过滤器来处理这两个问题type = 'u'（实际上您根本不需要sys.objects）。这是一个带有其他一些小改进的重写：

    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. 尝试将其包装在一个内联表值函数中

    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

    然后加入表信息模式

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

    用于获取视图中的列，如您所说，更改查询信息模式VIEW_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

相关问题