November 18, 2014 at 4:15 pm
JAG-360157 (11/15/2007)
Hi Kris,this is the same query for SQL 2005:
select SO.name as 'table', SC.name as field,
ST.name as datatype, SC.max_length as 'size',
sp.value as 'description'
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 left join
sys.extended_properties sp on sp.major_id = so.object_id and sp.minor_id = SC.column_id
and sp.name = 'MS_Description'
where SO.type = 'U'
order by SO.name, SC.name
Jose, you don't seem to be coming to the forum anymore, but I wanted to thank you for your code. 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 and commenting out the sp.value display as I didn't want it, and added the following line to show nullability:
case sc.is_nullable when 0 then 'NOT NULL' else 'NULL' end as Nullable
This code works in 2012, you might need to remove the underscore for older versions. But if you're running 2012, you can always use Information_Schema.Columns. I'm working on an expanded version, and I need to check the code libraries, we'll see what I come up with. I like working with system tables and views, so I'll probably try to do it myself.
-----
[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 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 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply