英文:
SSMS sorting result grid columns
问题
在SSMS（v18）中查看结果网格（通过右键单击表并选择编辑前X行），是否有一种方法可以按字母顺序对列进行排序？
我们有一个具有许多列的表格，导航到正确的列是一项巨大的工作。
英文:
In SSMS (v18) when viewing the result grid (by right clicking on a table and selecting Edit top X rows), is there a way to sort the columns alphabetically?
We have a table with many columns and navigating to the right one is a huge effort.
答案1
得分: 1
这段代码将创建一个针对特定表的视图，但会以字母顺序返回所有列。您可以稍后进行编辑，或者只保留选择语句。希望对您有所帮助。
DECLARE @tableName NVARCHAR(128) = 'yourTableName'
DECLARE @schemaName NVARCHAR(128) = 'yourSchemaName'
DECLARE @databaseName NVARCHAR(128) = 'yourDatabaseName'
DECLARE @viewName NVARCHAR(128) = 'yourViewName'
DECLARE @query NVARCHAR(MAX) = ''
SELECT @query += QUOTENAME(COLUMN_NAME) + ', '
FROM (
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schemaName
AND TABLE_NAME = @tableName
AND TABLE_CATALOG = @databaseName
ORDER BY COLUMN_NAME
) AS T
-- 移除最后的逗号和空格
SET @query = LEFT(@query, LEN(@query)-1)
-- 创建动态SQL以创建视图
SET @query = N'CREATE VIEW ' + QUOTENAME(@databaseName) + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@viewName) +
' AS SELECT ' + @query + ' FROM ' + QUOTENAME(@databaseName) + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)
-- 执行动态SQL
EXEC sp_executesql @query
英文:
This script will create a view for a specific table, but return all columns in alphabetical order. You can edit that later. Or you can keep just the select statement. Hope it helps
DECLARE @tableName NVARCHAR(128) = 'yourTableName'
DECLARE @schemaName NVARCHAR(128) = 'yourSchemaName'
DECLARE @databaseName NVARCHAR(128) = 'yourDatabaseName'
DECLARE @viewName NVARCHAR(128) = 'yourViewName'
DECLARE @query NVARCHAR(MAX) = ''
SELECT @query += QUOTENAME(COLUMN_NAME) + ', '
FROM (
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schemaName
AND TABLE_NAME = @tableName
AND TABLE_CATALOG = @databaseName
ORDER BY COLUMN_NAME
) AS T
-- Remove the last comma and space
SET @query = LEFT(@query, LEN(@query)-1)
-- Create the dynamic SQL to create the view
SET @query = N'CREATE VIEW ' + QUOTENAME(@databaseName) + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@viewName) +
' AS SELECT ' + @query + ' FROM ' + QUOTENAME(@databaseName) + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)
-- Execute the dynamic SQL
EXEC sp_executesql @query
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论