November 18, 2014 at 5:43 pm
Wayne West (11/18/2014)
I've made two minor tweaks, commenting out the sc.name on the Order By so that the fields display in the order of the structure of the table
I just wanted to note that it's not guaranteed that you'll get the columns displayed in the correct order. To ensure that, you need to include the ordinal position in the order by.
SELECT So.Name AS 'table',
Sc.Name AS Field,
St.Name AS Datatype,
Sc.Max_Length AS 'size',
CASE Sc.Is_Nullable
WHEN 0 THEN 'NOT NULL'
ELSE 'NULL'
END AS Nullable
FROM Sys.Columns Sc
INNER JOIN Sys.Objects So
ON Sc.Object_Id = So.Object_Id
INNER JOIN Sys.Types St
ON St.User_Type_Id = Sc.User_Type_Id
WHERE So.Type = 'U'
ORDER BY So.Name, COLUMNPROPERTY( SC.Object_Id, SC.Name, 'ordinal' );
UPDATE: It seems that the 'ordinal' parameter is undocumented as the others properties used in Information_Schema.Columns
November 19, 2014 at 8:37 am
Luis Cazares (11/18/2014)
Wayne West (11/18/2014)
I've made two minor tweaks, commenting out the sc.name on the Order By so that the fields display in the order of the structure of the tableI just wanted to note that it's not guaranteed that you'll get the columns displayed in the correct order. To ensure that, you need to include the ordinal position in the order by.
...
UPDATE: It seems that the 'ordinal' parameter is undocumented as the others properties used in Information_Schema.Columns
True. In the code that I'm working on, I have it by ordinal position. I'm not sure why you'd want the field names alphabetical, that's kinda baffling me.
I don't remember when Information_Schema came in to play, I think with SQL 2005, so when this thread started, they were probably still on 2000. But I'm using Information_Schema in the code that I'm working on, so hopefully it'll have long legs.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 19, 2014 at 8:45 am
Wayne West (11/19/2014)
Luis Cazares (11/18/2014)
Wayne West (11/18/2014)
I've made two minor tweaks, commenting out the sc.name on the Order By so that the fields display in the order of the structure of the tableI just wanted to note that it's not guaranteed that you'll get the columns displayed in the correct order. To ensure that, you need to include the ordinal position in the order by.
...
UPDATE: It seems that the 'ordinal' parameter is undocumented as the others properties used in Information_Schema.Columns
True. In the code that I'm working on, I have it by ordinal position. I'm not sure why you'd want the field names alphabetical, that's kinda baffling me.
I don't remember when Information_Schema came in to play, I think with SQL 2005, so when this thread started, they were probably still on 2000. But I'm using Information_Schema in the code that I'm working on, so hopefully it'll have long legs.
You should be safe using Information_Schema as those views were introduced to comply with SQL-92 Standard. The problem you could face is not having enough information on them that is available on system views.
Viewing 3 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply