column info in SQL 2000

which objects holds the checks and DFs? in 2000
LVL 5
25112Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jorgedeoliveiraborgesConnect With a Mentor Commented:
sysobjects (table)
Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.

xtype (column) char(2),  Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

http://tarakizer.com/files/systbl.zip
http://weblogs.sqlteam.com/tarad/archive/2004/04/02/1198.aspx
0
 
25112Author Commented:
if you look at the query and the results, you will see that default and constraint comes from the same table.. but once constraint is populated fine..and other 2 times default and constriants are the same.. it should not be.. what is wrong..

also can you check if the query is wholesome?
Untitled.jpg
0
 
25112Author Commented:
the query is...
select
  o.id  TableObjectID,c.colorder ColOrder,t.[name] ColumnType,c.[length] ColLen,sc1.text [Default],sc2.text [Constraint]
from 
sysobjects o 
join sysusers u on o.uid = u.uid
join syscolumns c on o.id = c.id 
join systypes t on c.xtype = t.xtype
left outer join syscomments sc1 on c.cdefault=sc1.id
left outer join sysconstraints const on const.id = c.id and const.colid = c.colid
left outer join syscomments sc2 on const.constid = sc2.id
where  o.name = 'tblRSXData' Order by 2

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
25112Author Commented:
jorgedeoliveiraborges, I am referring to default and check constraints which are at column level and not table level.
0
 
25112Author Commented:
i just added owner info and also key_column_usage but then it does not even bring anything.. can you see what is wrong..

key_column_usage is needed to bring ordinal position of PKs alone.. other columns could have a null there..
select
  user_name(o.uid ), o.id TableObjectID,c.colorder ColOrder,t.[name] ColumnType,c.[length] ColLen,sc1.text [Default],sc2.text [Constraint]
,kc.ordinal_position 
from 
sysobjects o 
join sysusers u on o.uid = u.uid
join syscolumns c on o.id = c.id 
join systypes t on c.xtype = t.xtype
left outer join syscomments sc1 on c.cdefault=sc1.id
left outer join sysconstraints const on const.id = c.id and const.colid = c.colid
left outer join syscomments sc2 on const.constid = sc2.id
left outer join information_schema.key_column_usage kc on kc.constraint_name = o.name and o.xtype = 'PK'
where  o.name = 'tblRSXData' Order by 3

Open in new window

0
 
Anthony PerkinsConnect With a Mentor Commented:
As stated previously they are in the sysobjects table.  However since you appear to be using SQL Server 2005, you should not be using system tables and instead you should use the corresponding catalog views.
0
 
LowfatspreadCommented:
maybe if you told use what you intend to do with the information we can suggest a better way of obtaining it?
0
 
25112Author Commented:
acperkins, this particular query is only for the SQL 2000 servers.

I am needing to pull out the column details for each tables for coulpe of apps.

my basic query is and I am doing many left joins to get other info for the columns...
select * from sysobjects  o join syscolumns c on o.id = c.id  and o.name = 'tblRSXData'

where I get stuck is in getting the check constraints, default constraints separately and also the PK ordinal_position

with my query, I am getting Default and constraint values as

Default      Constraint
NULL      ([GenderCode] = 'M' or [GenderCode] = 'F')
NULL      NULL
(getdate())      (getdate())
(suser_sname())      (suser_sname())

the first row is correct, but the last 2 in the above example is not right- because they are the defaults not the constraints..

also the left join for PK ordinal_position brings back NULLs .. but the following does bring back the PK column's values.. but put in the bigger query fails..

select column_name,ordinal_position from sysobjects o left outer join information_schema.key_column_usage kc on kc.constraint_name = o.name and o.xtype = 'PK' where table_name = 'tblRSXData'
0
 
25112Author Commented:

as i mentioned the below is OK.. brings back one record..
select column_name,ordinal_position from sysobjects o left outer join information_schema.key_column_usage kc on kc.constraint_name = o.name and
o.xtype = 'PK' where table_name = 'tblRSXData'

but the following
select column_name, ordinal_position from sysobjects o join syscolumns c on o.id = c.id
left outer join information_schema.key_column_usage kc on kc.constraint_name = o.name and o.xtype = 'PK'
where o.name =  'tblRSXData'
brings back 12 NULLs for both columns

what is missing..i thought if column also needs joined to kc... and hence i tried below..

select kc1.column_name, kc1.ordinal_position from sysobjects o join syscolumns c on o.id = c.id
left outer join information_schema.key_column_usage kc on kc.constraint_name = o.name and o.xtype = 'PK'
left outer join information_schema.key_column_usage kc1 on kc1.column_name = c.name
where o.name =  'tblRSXData'
brings back 41 rows of values for both columns- does not seem right...
0
 
25112Author Commented:
select sc1.id,sc1.text from sysobjects  o join syscolumns c on o.id = c.id  and o.name = 'tblRSXData'
left outer join syscomments sc1 on c.cdefault = sc1.id

brings back

id      text
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
2114158627      (getdate())
2130158684      (suser_sname())
NULL      NULL
NULL      NULL

select sc2.id,sc2.text from sysobjects  o join syscolumns c on o.id = c.id  and o.name = 'tblRSXData'
left outer join syscomments sc1 on c.cdefault=sc1.id
left outer join sysconstraints const on const.id = c.id and const.colid = c.colid
left outer join syscomments sc2 on const.constid = sc2.id

brings back

id      text
2114158627      (getdate())
2130158684      (suser_sname())
30675207      ([GenderCode] = 'M' or [GenderCode] = 'F')
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL
NULL      NULL

because both queries bring back values for 2114158627& 2130158684 is the problem.. ideally, the first query is right, and the second should  have values only for 30675207      

to summarize the question asked, I am requesting your input on this and the issue above in post # 36579241.
0
 
25112Author Commented:

select c.cdefault from sysobjects  o join syscolumns c on o.id = c.id  and o.name = 'tblRSXData' gives

0
0
0
0
0
0
0
0
2114158627
2130158684
0
0


select sc1.text,sc2.text,sc1.ctext,sc2.ctext  from sysobjects  o join syscolumns c on o.id = c.id  and o.name = 'tblRSXData'
left outer join syscomments sc1 on c.cdefault=sc1.id
left outer join sysconstraints const on const.id = c.id and const.colid = c.colid
left outer join syscomments sc2 on const.constid = sc2.id

gives  the below
Untitled.jpg
0
 
Anthony PerkinsCommented:
I am not following you.  Can you just keep it simple by stating your current query, show the output and expected output?
0
 
25112Author Commented:
sounds good..
the query right now is : (see attached for current and expected results..)

in this example, the PK is only made of one column.. but for example, if the PK was made of 4 composite columns (let's say ColOrder 1,2,3,4), then the first 4 rows would have 1,2,3,4 in PK Order based on information_schema.key_column_usage

select
user_name(o.uid ) [Schema],
o.id TableObjectID,
c.colorder ColOrder,
t.[name] ColumnType,
c.[length] ColLen,
sc1.text [Default],
sc2.text [Constraint],
kc.ordinal_position PKPosition
from
sysobjects o
join syscolumns c on o.id = c.id
join systypes t on c.xtype = t.xtype
left outer join syscomments sc1 on c.cdefault=sc1.id
left outer join sysconstraints const on const.id = c.id and const.colid = c.colid
left outer join syscomments sc2 on const.constid = sc2.id
left outer join information_schema.key_column_usage kc on kc.constraint_name = o.name and o.xtype = 'PK'
where  o.name = 'tblRSXData' Order by 3

s.bmp
0
 
Anthony PerkinsCommented:
I am afraid I cannot see the problem. You are going to have to post the full CREATE TABLE statement for your table "tblRSXData".
0
 
25112Author Commented:
thank you
CREATE TABLE [dbo].[tblRSXData](
	[RSXDataPK] [int] IDENTITY(1,1) NOT NULL,
	[AgencyFK] [int] NOT NULL,
	[ManagerFK] [int] NOT NULL,
	[CountProbe] [char](8) NOT NULL,
	[SendingAgencyFK] [int] NULL,
	[EthnicCodeFK] [char](1) NOT NULL,
	[GenderCode] [char](1) NOT NULL,
	[ManagingLevelFK] [char](3) NOT NULL,
	[AuditAddDate] [datetime] NOT NULL CONSTRAINT [DF_tblRSXData_AuditAddDate]  DEFAULT (getdate()),
	[AuditAddUserName] [varchar](24) NOT NULL CONSTRAINT [DF_tblRSXData_AuditAddUserName]  DEFAULT (suser_sname()),
	[AuditChangeDate] [datetime] NULL,
	[AuditChangeUserName] [varchar](24) NULL
 CONSTRAINT [PK_tblRSXDatas] PRIMARY KEY NONCLUSTERED 
(
	[RSXDataPK] ASC
))


ALTER TABLE [dbo].tblRSXData  WITH CHECK ADD  CONSTRAINT [CK_GenderCode] CHECK  (([GenderCode] = 'M' or [GenderCode] = 'F'))
GO

Open in new window

0
 
Anthony PerkinsCommented:
I am getting a different output to the one in your image.  As you can clearly see below, the constraints do show up in my result set:
Schema	TableObjectID	ColOrder	ColumnType	ColLen	Default	Constraint	PKPosition
dbo	820197972	1	int	4	NULL	NULL	NULL
dbo	820197972	2	int	4	NULL	NULL	NULL
dbo	820197972	3	int	4	NULL	NULL	NULL
dbo	820197972	4	char	8	NULL	NULL	NULL
dbo	820197972	5	int	4	NULL	NULL	NULL
dbo	820197972	6	char	1	NULL	NULL	NULL
dbo	820197972	7	char	1	NULL	([GenderCode]='M' OR [GenderCode]='F')	NULL
dbo	820197972	8	char	3	NULL	NULL	NULL
dbo	820197972	9	datetime	8	(getdate())	(getdate())	NULL
dbo	820197972	10	varchar	24	(suser_sname())	(suser_sname())	NULL
dbo	820197972	11	datetime	8	NULL	NULL	NULL
dbo	820197972	12	varchar	24	NULL	NULL	NULL

Open in new window

0
 
Anthony PerkinsCommented:
Hopefully this will be a tad clearer:
Schema	TableObjectID	ColOrder	ColumnType	ColLen		Default		Constraint				PKPosition
dbo	820197972		1	int		4		NULL		NULL					NULL
dbo	820197972		2	int		4		NULL		NULL					NULL
dbo	820197972		3	int		4		NULL		NULL					NULL
dbo	820197972		4	char		8		NULL		NULL					NULL
dbo	820197972		5	int		4		NULL		NULL					NULL
dbo	820197972		6	char		1		NULL		NULL					NULL
dbo	820197972		7	char		1		NULL		([GenderCode]='M' OR [GenderCode]='F')	NULL
dbo	820197972		8	char		3		NULL		NULL					NULL
dbo	820197972		9	datetime		8		(getdate())	(getdate())				NULL
dbo	820197972		10	varchar		24		(suser_sname())	(suser_sname())				NULL
dbo	820197972		11	datetime		8		NULL		NULL					NULL
dbo	820197972		12	varchar		24		NULL		NULL					NULL

Open in new window

0
 
25112Author Commented:
acperkins, thanks for checking.. there are 2 issues:

1)PKPosition 1 is not showing up for the PK Column. (It is showing NULL) ColOrder 1 is the column that has the PK.

2)ColOrder 9&10 do not have constraints, only defaults.. but the defaults are showing up in constraints also.

otherwise it is good..
0
 
Anthony PerkinsCommented:
2) A Default is a Constraint.  If you don't want it to show up in that column than you can do something like this (but to be honest I have not really thought it through):
SELECT  USER_NAME(o.uid) [Schema],
        o.id TableObjectID,
        c.colorder ColOrder,
        t.[name] ColumnType,
        c.[length] ColLen,
        sc1.text [Default],
        CASE 
			WHEN sc1.text IS NULL THEN sc2.text
			ELSE NULL
		END [Constraint],
        kc.ordinal_position PKPosition
FROM    sysobjects o
        JOIN syscolumns c ON o.id = c.id
        JOIN systypes t ON c.xtype = t.xtype
        LEFT OUTER JOIN syscomments sc1 ON c.cdefault = sc1.id
        LEFT OUTER JOIN sysconstraints const ON const.id = c.id
                                                AND const.colid = c.colid
        LEFT OUTER JOIN syscomments sc2 ON const.constid = sc2.id
        LEFT OUTER JOIN information_schema.key_column_usage kc ON kc.constraint_name = o.name
                                                                  AND o.xtype = 'PK'
WHERE   o.name = 'tblRSXData'
ORDER BY 3

Open in new window

0
 
25112Author Commented:
hmm.. OK.. so you would say that Default and Constraint showing the same record, after all, is just right and correct?

what about

select column_name,ordinal_position from sysobjects o left outer join information_schema.key_column_usage kc
on kc.constraint_name = o.name and
o.xtype = 'PK' where kc.table_name = 'tblRSXData'  

brings back the record for the PK .. but in the query above, brings NULL when used with other LEFT JOINS?
0
 
Anthony PerkinsCommented:
I am sorry I have not used those old system objects in more than 5 years and for the life of me I don't recall how they are linked together.

Hopefully someone will step up to the plate.
0
 
25112Author Commented:
Thanks for your effort this far.. if it is OK, let me ask the mods for extra attention, since this thread is not new and may get less attention than the new questions..
0
 
Kevin CrossChief Technology OfficerCommented:
I am in the same boat as acperkins. I have only had newer version of SQL since 2004. However, as a note even the new INFORMATION_SCHEMA behaves as the SQL 2000 documentation for INFORMATION_SCHEMA with regard to CHECK_CONSTRAINTS, i.e., CONSTRAINT_NAME is value like CK_GenderCode and not the actual column name and does not match to that if that is what you are trying. I will take a look and see what else is going on.
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, similar to what I thought:

You are filtering sysobject or "o" for o.name = 'tblRSXData', then in the LEFT JOIN comparing kc.constraint_name = o.name ; if o.name is equal to the table_name, it will NOT also equal the constraint_name.
0
 
Kevin CrossChief Technology OfficerCommented:
See this:
-- http://msdn.microsoft.com/en-us/library/ms189783.aspx
SELECT USER_NAME(o.uid) [Schema]
     , o.id TableObjectID
     -- , c.name ColumnName
     , c.colorder ColOrder
     , t.name ColumnType
     , sc1.text [Default]
     , NULLIF(sc2.text, sc1.text) [Constraint]
     , kc.ORDINAL_POSITION PKPosition
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
JOIN systypes t ON t.xtype = c.xtype
LEFT JOIN syscomments sc1 ON sc1.id = c.cdefault
LEFT JOIN (
   sysconstraints cc
   JOIN syscomments sc2 ON cc.constid = sc2.id
) ON cc.id = c.id AND cc.colid = c.colid
LEFT JOIN information_schema.key_column_usage kc 
      ON kc.TABLE_NAME = o.name AND kc.COLUMN_NAME = c.name
WHERE o.name = 'tblRSXData'
ORDER BY ColOrder
;

Open in new window

0
 
25112Author Commented:
thanks a lot-

so, if you add "AND o.xtype = 'PK'" now, it does not populate PKPosition? why not?
SELECT USER_NAME(o.uid) [Schema]
     , o.id TableObjectID
     -- , c.name ColumnName
     , c.colorder ColOrder
     , t.name ColumnType
     , sc1.text [Default]
     , NULLIF(sc2.text, sc1.text) [Constraint]
     , kc.ORDINAL_POSITION PKPosition
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
JOIN systypes t ON t.xtype = c.xtype
LEFT JOIN syscomments sc1 ON sc1.id = c.cdefault
LEFT JOIN (
   sysconstraints cc
   JOIN syscomments sc2 ON cc.constid = sc2.id
) ON cc.id = c.id AND cc.colid = c.colid
LEFT JOIN information_schema.key_column_usage kc 
      ON kc.TABLE_NAME = o.name AND kc.COLUMN_NAME = c.name AND o.xtype = 'PK'
WHERE o.name = 'tblRSXData'
ORDER BY ColOrder

Open in new window

0
 
25112Author Commented:
also, could you explain the significance of the join within the left join, as against the bunch of left joins.

LEFT JOIN (
   sysconstraints cc
   JOIN syscomments sc2 ON cc.constid = sc2.id
) ON cc.id = c.id AND cc.colid = c.colid
LEFT JOIN information_schema.key_column_usage kc
0
 
Kevin CrossChief Technology OfficerCommented:
Because o.xtype = 'PK' is not valid for the TABLE. Remember, you have filtered sysobjects o to the table object. If you want the columns, you will have to self-join the sysobjects table ... then you will have xtype = 'PK' for the primary key column(s).

Regarding this the JOINs, it is probably habit. Since syscomments sc2 is only valid when you have sysconstraints cc row, I tend to INNER JOIN those separately then LEFT JOIN the result. The query optimizer probably treats them both the same, so I am hesitant to claim this performs better but that is part of the original thought process ... then as I said, it is likely now just habit.
LEFT JOIN (
   sysconstraints cc
   JOIN syscomments sc2 ON cc.constid = sc2.id
) ON cc.id = c.id AND cc.colid = c.colid
0
 
25112Author Commented:
I am following you- thanks very much. it is good to know and realize..

I need to add one last column to the report.. wanted to get the others cleared before coming to that..

the extended property is not showing up, after a left join.. could you see what i am missing?
EXEC   sp_addextendedproperty 'MS_Description', 'Links to Agency Table - is required', 'user', dbo, 'table', tblRSXData, 'column', AgencyFK

SELECT USER_NAME(o.uid) [Schema]
     , o.id TableObjectID
     -- , c.name ColumnName
     , c.colorder ColOrder
     , t.name ColumnType
     , sc1.text [Default]
     , NULLIF(sc2.text, sc1.text) [Constraint]
     , kc.ORDINAL_POSITION PKPosition
	 , cast(e.value as varchar(500)) as 'Description'
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
JOIN systypes t ON t.xtype = c.xtype
LEFT JOIN syscomments sc1 ON sc1.id = c.cdefault
LEFT JOIN (
   sysconstraints cc
   JOIN syscomments sc2 ON cc.constid = sc2.id
) ON cc.id = c.id AND cc.colid = c.colid
LEFT JOIN information_schema.key_column_usage kc 
      ON kc.TABLE_NAME = o.name AND kc.COLUMN_NAME = c.name 
LEFT JOIN ::FN_LISTEXTENDEDPROPERTY('MS_Description','user','dbo','table', DEFAULT, 'column', null) e on c.name COLLATE DATABASE_DEFAULT = e.objname COLLATE DATABASE_DEFAULT
WHERE o.name = 'tblRSXData'
ORDER BY ColOrder

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Here this should get it for you. Just remember when closing this that others above answered the original question of "which objects holds the checks and DFs? in 2000."
SELECT USER_NAME(o.uid) [Schema]
     , o.id TableObjectID
     -- , c.name ColumnName
     , c.colorder ColOrder
     , t.name ColumnType
     , sc1.text [Default]
     , NULLIF(sc2.text, sc1.text) [Constraint]
     , kc.ORDINAL_POSITION PKPosition
	 , CAST(e.value AS VARCHAR(500)) [Description]
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
JOIN systypes t ON t.xtype = c.xtype
LEFT JOIN syscomments sc1 ON sc1.id = c.cdefault
LEFT JOIN (
   sysconstraints cc
   JOIN syscomments sc2 ON cc.constid = sc2.id
) ON cc.id = c.id AND cc.colid = c.colid
LEFT JOIN information_schema.key_column_usage kc 
       ON kc.TABLE_NAME = o.name AND kc.COLUMN_NAME = c.name 
LEFT JOIN FN_LISTEXTENDEDPROPERTY('MS_Description','user','dbo','table', 'tblRSXData', 'column', null) e 
       ON c.name COLLATE DATABASE_DEFAULT = e.objname
WHERE o.name = 'tblRSXData'
ORDER BY ColOrder
;

Open in new window

0
 
25112Author Commented:
I surely will, and you are very considerate.

In the query,  I get "Invalid object name 'FN_LISTEXTENDEDPROPERTY'." but still pink colored.. is it ok to use in sql 2000?

if i replace with
::FN_LISTEXTENDEDPROPERTY
then description is blank..

also could we put DEFAULT instead of 'tblRSXData' in the ::FN_LISTEXTENDEDPROPERTY because, i ultimately want to run this for all the tables in one shot, and want to removed the where condition.. (I did the filter to just play with and confirm the results and also easy to share the code and example with the experts..)
0
 
Kevin CrossChief Technology OfficerCommented:
I will have to double check. ::FN_LISTEXTENDEDPROPERTY and FN_LISTEXTENDEDPROPERTY for SQL 2000, but both worked for me in newer version of SQL. DEFAULT does not appear to work and not sure how you get around it as you cannot pass the table name from the query itself. You can try sp_msforeachtable or something in the dynamic sql realm. Good luck!
0
 
Anthony PerkinsCommented:
>>I get "Invalid object name 'FN_LISTEXTENDEDPROPERTY'." but still pink colored.. is it ok to use in sql 2000?<<
::fn_listextendedproperty is the correct syntax.

>>then description is blank..<<
What do you get when you do this:
SELECT *
FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description', 'user', 'dbo', 'table', 'tblRSXData', 'column', null)

>>also could we put DEFAULT instead of 'tblRSXData' in the ::FN_LISTEXTENDEDPROPERTY because<<
You want it to match the table you are querying, so if you do that you will have to add the table to the JOIN.
0
 
Kevin CrossChief Technology OfficerCommented:
acperkins,

SELECT *
FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description', 'user', 'dbo', 'table', 'tblRSXData', 'column', null)

Works. What does not is:

SELECT *
FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description', 'user', 'dbo', 'table', default, 'column', null)

You get an empty record set.

I do not have an answer for that other than using dynamic SQL to pass table name. If it worked, then you can join on table, but it appears that the extended property is needed at the column level. As such, the results of listing the extended property is:

COLUMN | AgencyFK | MS_Description | Links to Agency Table - is required

i.e., no table name specified.
0
 
Anthony PerkinsCommented:
I see what you mean.  Boy I am glad we don't have to use that any more!
0
 
Kevin CrossChief Technology OfficerCommented:
*smile* Agreed. In honor of the number of questions we have participated in recently using sp_msforeachtable.
sp_msforeachtable '
SELECT USER_NAME(o.uid) [Schema]
     , o.id TableObjectID
     , c.colorder ColOrder
     , t.name ColumnType
     , sc1.text [Default]
     , NULLIF(sc2.text, sc1.text) [Constraint]
     , kc.ORDINAL_POSITION PKPosition
	 , CAST(e.value AS VARCHAR(500)) [Description]
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
JOIN systypes t ON t.xtype = c.xtype
LEFT JOIN syscomments sc1 ON sc1.id = c.cdefault
LEFT JOIN (
   sysconstraints cc
   JOIN syscomments sc2 ON cc.constid = sc2.id
) ON cc.id = c.id AND cc.colid = c.colid
LEFT JOIN information_schema.key_column_usage kc 
       ON kc.TABLE_NAME = o.name AND kc.COLUMN_NAME = c.name 
LEFT JOIN FN_LISTEXTENDEDPROPERTY(''MS_Description'',''user'', PARSENAME(''?'', 2),''table'', PARSENAME(''?'', 1), ''column'', null) e 
       ON c.name COLLATE DATABASE_DEFAULT = e.objname
WHERE o.xtype = ''U'' AND o.name = PARSENAME(''?'', 1)
ORDER BY ColOrder;
';

Open in new window

0
 
Anthony PerkinsCommented:
Yes. I thought of that.  But what a mess!

And I guess the author would prefer that in a single resultset.  If so, what they can do is just use Dynamic SQL for the extended properties and place the result in a temp table, they can then use that to JOIN to the rest of the table.
0
 
25112Author Commented:

>>SELECT * FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description', 'user', 'dbo', 'table', default, 'column', null) You get an empty record set.

is this a bug?
there is an example in http://msdn.microsoft.com/en-us/library/ms179853.aspx
(see under "C. Displaying extended properties on all tables in a schema")
i tried that format and put the schema as dbo ( I am running this in 2008 R2 just to make sure the format/syntax is right, then I can try it in 2000 with  ::fn_listextendedproperty

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL);

but still did not get it.
0
 
25112Author Commented:
mwvisa1,
with the code in 36714245, in 2008R2, I get the error
"
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '
SELECT USER_NAME(o.uid) [Schema]
     , o.id TableObjectID
     , c.colorder ColOrder
     , t.name ColumnType
     , sc1.t'.
"

in 2000, i get (a bunch of) the error
"
Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'PARSENAME'.
"
0
 
25112Author Commented:
>>And I guess the author would prefer that in a single resultset.  If so, what they can do is just use Dynamic SQL for the extended properties and place the result in a temp table, they can then use that to JOIN to the rest of the table.

agreed. thanks!
0
 
Kevin CrossChief Technology OfficerCommented:
25112: that example works because it is listing extended properties for all tables, i.e., the extended property is defined at the table. In your case, you are defining the property at a column; therefore, the fact that it returns blank at table level makes sense to me.

As far as the error, please post the exact code that you tried. I tested, but am on newer version of SQL. The table name was like schema.tablename. If yours is not coming up like that, it may be failing due to pulling null ... anyway, post what you are using.
0
 
25112Author Commented:
>>that example works because it is listing extended properties for all tables,

so "Displaying extended properties on all columns for all tables" is not a possibility with fn_listextendedproperty, right?
0
 
25112Author Commented:
my fault: your code 36714245 works flawlessly in 2005 and 2008..

but in 2000, i get the
Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'PARSENAME'.
error, for the same code that works good in 2005/R2..
0
 
Anthony PerkinsCommented:
>>Line 20: Incorrect syntax near 'PARSENAME'.<<
That is correct.  The functionality of fn_listextendedproperty appears to have changed.  You cannot do this in SQL Server 2000:
Select *
From ::FN_LISTEXTENDEDPROPERTY('MS_Description','user', PARSENAME('[dbo].[TableName]', 2), 'table', PARSENAME('[dbo].[TableName]', 1), 'column', null)

0
 
25112Author Commented:
mwvisa1, since we removed AND o.xtype = 'PK' condition, PKPosition is howing Non clustered index's orders too.. is there a way we can introduce the condition so that the query is right and also we get only info for PK..
0
 
25112Author Commented:
>>That is correct.  The functionality of fn_listextendedproperty appears to have changed.  You cannot do this in SQL Server 2000:

select PARSENAME('[dbo].[tblRSXData]', 2)
select PARSENAME('[dbo].[tblRSXData]', 1)

themselves work in 2000.. but not within the parameter string..
0
 
Anthony PerkinsCommented:
>>themselves work in 2000.. but not within the parameter string.. <<
Of course it does, it just cannot be used that way with fn_listextendedproperty()
0
 
Kevin CrossChief Technology OfficerCommented:
Regarding http:#36719296, I thought I answered you already, so my apologies on the delay. You would have to LEFT OUTER JOIN in another instance of sysobjects with a different aliases that is filtered to xtype = 'PK' and associated to the column it matches to. You can use the [name] column of that sysobjects to join to the [constraint_name] of information_schema.key_column_usage.

acperkins took care of answering the other bit on the extended properties. It has been awhile with SQL 2000 as said earlier, so my apologies as I forget what things worked there differently.
0
 
25112Author Commented:
mwvisa1,
>>You would have to LEFT OUTER JOIN in another instance of sysobjects with a different aliases that is filtered to xtype = 'PK' and associated to the column it matches to. You can use the [name] column of that sysobjects to join to the [constraint_name] of information_schema.key_column_usage.

I am sorry for the delay.

I did not have time to get back to you after I tried the above suggestion.. but to make the deadline, i tried the below and it worked..
~~~~~~~~~~~
LEFT JOIN information_schema.key_column_usage kc ON kc.TABLE_NAME = o.name AND kc.COLUMN_NAME = c.name
to
LEFT JOIN
(select kc.* from sysobjects so, information_schema.key_column_usage kc
where so.name = kc.constraint_name and so.xtype = 'PK') kc
ON kc.TABLE_NAME = o.name AND kc.COLUMN_NAME = c.name
~~~~~~~~~~~

could you help me with the right "left join"
>>--LEFT JOIN sysobjects so on so.name = kc.constraint_name and so.xtype = 'PK' << was the best i could think of doing it, but it is not it..

thanks again for your help in this and guiding me to complete the required. but if you can help me out with the above join, i could use that for the next time round, instead of the innerjoin+leftjoin
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
What you have is the general idea. You INNER JOIN the constraint and sysobjects and then LEFT JOIN the combination to your query. A simple transition from what you have is this:

LEFT JOIN (
   sysobjects so
   JOIN information_schema.key_column_usage kc
      ON so.name = kc.constraint_name and so.xtype = 'PK'
) ON kc.TABLE_NAME = o.name AND kc.COLUMN_NAME = c.name

So, you should be set.
0
 
25112Author Commented:
thanks kindly for confirming it..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.