Solved

column info in SQL 2000

Posted on 2011-09-21
52
380 Views
Last Modified: 2012-06-21
which objects holds the checks and DFs? in 2000
0
Comment
Question by:25112
  • 26
  • 12
  • 11
  • +2
52 Comments
 
LVL 5

Author Comment

by:25112
ID: 36576962
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
 
LVL 5

Author Comment

by:25112
ID: 36576967
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
 
LVL 6

Accepted Solution

by:
jorgedeoliveiraborges earned 167 total points
ID: 36577020
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
 
LVL 5

Author Comment

by:25112
ID: 36577362
jorgedeoliveiraborges, I am referring to default and check constraints which are at column level and not table level.
0
 
LVL 5

Author Comment

by:25112
ID: 36577487
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 36577728
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36578020
maybe if you told use what you intend to do with the information we can suggest a better way of obtaining it?
0
 
LVL 5

Author Comment

by:25112
ID: 36579182
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
 
LVL 5

Author Comment

by:25112
ID: 36579241

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
 
LVL 5

Author Comment

by:25112
ID: 36579298
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
 
LVL 5

Author Comment

by:25112
ID: 36579352

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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36580126
I am not following you.  Can you just keep it simple by stating your current query, show the output and expected output?
0
 
LVL 5

Author Comment

by:25112
ID: 36580357
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36584427
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
 
LVL 5

Author Comment

by:25112
ID: 36584601
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36590869
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36590892
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
 
LVL 5

Author Comment

by:25112
ID: 36592603
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36592757
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
 
LVL 5

Author Comment

by:25112
ID: 36593243
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36593811
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
 
LVL 5

Author Comment

by:25112
ID: 36594234
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36600948
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36600976
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36601220
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:25112
ID: 36601385
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
 
LVL 5

Author Comment

by:25112
ID: 36601391
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36601481
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
 
LVL 5

Author Comment

by:25112
ID: 36709249
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36710830
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
 
LVL 5

Author Comment

by:25112
ID: 36711737
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36712113
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36712143
>>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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36712215
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36714167
I see what you mean.  Boy I am glad we don't have to use that any more!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36714245
*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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36714334
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
 
LVL 5

Author Comment

by:25112
ID: 36716405

>>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
 
LVL 5

Author Comment

by:25112
ID: 36716444
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
 
LVL 5

Author Comment

by:25112
ID: 36716450
>>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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36716512
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
 
LVL 5

Author Comment

by:25112
ID: 36716947
>>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
 
LVL 5

Author Comment

by:25112
ID: 36716995
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36718862
>>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
 
LVL 5

Author Comment

by:25112
ID: 36719296
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
 
LVL 5

Author Comment

by:25112
ID: 36719318
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36743443
>>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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36913587
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
 
LVL 5

Author Comment

by:25112
ID: 36964382
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
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 167 total points
ID: 36964668
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
 
LVL 5

Author Comment

by:25112
ID: 36972288
thanks kindly for confirming it..
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Group by and order by clause 28 36
GRANT, REVOKE, DENY 4 17
SQL Inner Join Vs SubQueries 9 24
How to simplify my SQL statement? 11 13
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now