May 20, 2014 at 9:17 am
There are about 300 fields in a views.
How to reorder all fields by alphabet?
For example, from script of views like below:
select name, date, amount from order
re-order fields' name like below:
select amount, date, name from order
For about 300 fields view, how to code to re-order fields' name?
May 20, 2014 at 9:57 am
Why would you do that?
There's nothing to gain in that option. You'd need to generate the scripts for each and every view again to create them.
What if you have complex views that won't use simple columns and are complex calculations that could involve CTEs? What if you have set operators (UNION, UNION ALL, EXCEPT, INTERSECT)? There are many things that can go wrong and the benefit seems pointless.
May 20, 2014 at 10:22 am
adonetok (5/20/2014)
For about 300 fields view, how to code to re-order fields' name?
ALTER VIEW ... then start typing. Don't forget to test all the applications and fix the myriad of things that break as a result.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2014 at 10:39 am
Why reorder the view?
You can just select the columns from the view in alphabetical order and be done with it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2014 at 10:49 am
The reason is:
I have user's Excel file including data in each fields (from this views) and were sorted by alphabet.
To find out matched fields between Excel file and views, I need to re-order views fieds.
May 20, 2014 at 10:51 am
No, you just need to select the fields in alpha order. That is far less impactful than to change the column order within the view.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2014 at 11:45 am
If you don't want to rewrite the SELECT to put the columns in order. You could profit of INFORMATION_SCHEMA views, Dynamic SQL and concatenation code.
This is an example to achieve it. ask any questions that you have after reading the following article.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
DECLARE @View varchar(128) = 'YourViewName'
DECLARE @sql nvarchar(MAX)
SELECT @sql = STUFF((SELECT CHAR(9) + ',' + COLUMN_NAME + CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @View
ORDER BY COLUMN_NAME
FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)'), 1, 2, 'SELECT ') + ' FROM ' + @View
PRINT @sql
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply