How to make the report show fields in multiple columns?

I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?

John Smith

Title: MSTP Location: Main Campus IP:127.0.0.1

Softwares:

Adobe Standard 7.0 Access 5.0

Internet Explore 6.0 Office XP

Any suggestion is appreciated.

There is an option in Crystal Reports -- "Format with multiple columns". I couldn't find the similar configuration in SQL reporting. There is a "Columns" setting in Body Properties, but it doesn't work as making the report in multiple columns. Please help!!

How to make table columns invisible

Hope this is the correct forum for this question. I have set up membership and roles, and have a login function. I am using a grid view and a form view to show some information that is unique to each user. In particular, I want the grid view to show the user’s client list (client first name, last name and email), and the form view to allow input by the user of client names and emails.

I have set up the grid view and connected it to a table in my data base. This is a table that includes UserName (which is also in the aspnet_Users table), Client ID (which gets set automatically by the program), clientFirstName, ClientLastname, and ClientEmail. I have a select statement of:

SELECT * FROM [Clients] WHERE ([UserName] = @.UserName)

I have checked off “Generate Insert, Update, and Delete Statements” and optimistic concurrency.

In the grid view, I’ve enabled editing and deleting in the menu task bar, and I’ve edited the columns to make ClientID and UserName “false” for the visible property (I don’t want them to show up when the user is viewing the web page).

But the problem is, when I run the application, when I try to edit a line and hit update, it won’t take the change. This does not happen if I set the UserName visible property to “true” (and so have UserName show up).

For the form view, I similarly want to have UserName and ClientID be invisible to the user.

I'm using Visual Studio 2005 Pro, and SQL Developer Edition.

Any tips on how I might do this would be appreciated! Thanks.

Tom,

You want to post this to asp.net forum. Basically, if you just bind your data resultset to a gridview, all columns will show up. To suppress the column, you need to modify the grid's column property or don't ask for the column in your sql query.|||

OJ,

Thanks much for your reply. I'll post on asp.net. It's strange, I tried to suppress the UserName column (through using "false" for the visible property), but when I do that I'm not able to edit entries when I run the application. And if I don't ask for the UserName column with the sql query, I run into the same problem. I tried using "UserId" instead of UserName in the query, but when I ran the app I got an error message that it's not part of the profile common class. I'll keep trying, thanks again.

Tom

|||Tom,

It's worth checking out info here. There are lots of info and examples for you to pick up.
http://www.asp.net/QuickStart/aspnet/doc/ctrlref/data/gridview.aspx

g'luck.|||Great, thanks OJ! I'll delve into that and see what I can come up with.

How to make some textboxes repeat with a table across pages

In the Body portion of a report I have a List. Within the List I have a
textbox. Under the textbox I have a table with various columns. It's
possible that the table spans more than one page, which is OK - it just
depends on how much data I have. What I'm having trouble doing is the
following:
When the table spans multiple pages, I want the textbox to print as well.
The textbox is like header info for the table. I have a client with
invoices. The invoice data is shown in the table. The client name in the
textbox.
I've tried the RepeatWith property on the textbox and set the value to the
name of the table, however, it still doesn't repeat. What am I missing'
ThxSo Adrian,
Did you ever get an answer or figure this issue out?
I could certainly use a solution.
Thx,
Russell
"Adrian Maull (MCP)" <no_spam@.no_email.org> wrote in message
news:eKAdWqZxEHA.3896@.TK2MSFTNGP10.phx.gbl...
> In the Body portion of a report I have a List. Within the List I have a
> textbox. Under the textbox I have a table with various columns. It's
> possible that the table spans more than one page, which is OK - it just
> depends on how much data I have. What I'm having trouble doing is the
> following:
> When the table spans multiple pages, I want the textbox to print as well.
> The textbox is like header info for the table. I have a client with
> invoices. The invoice data is shown in the table. The client name in the
> textbox.
> I've tried the RepeatWith property on the textbox and set the value to the
> name of the table, however, it still doesn't repeat. What am I missing'
> Thx
>|||No answer yet. I'm currently looking for other Report Services newsgroups.
This one doesn't seem to be monitored very well.
"Russell Hardie" <russell_hardie@.cranevalve.com> wrote in message
news:%23ex51%23pxEHA.1308@.TK2MSFTNGP09.phx.gbl...
> So Adrian,
> Did you ever get an answer or figure this issue out?
> I could certainly use a solution.
> Thx,
> Russell
> "Adrian Maull (MCP)" <no_spam@.no_email.org> wrote in message
> news:eKAdWqZxEHA.3896@.TK2MSFTNGP10.phx.gbl...
> > In the Body portion of a report I have a List. Within the List I have a
> > textbox. Under the textbox I have a table with various columns. It's
> > possible that the table spans more than one page, which is OK - it just
> > depends on how much data I have. What I'm having trouble doing is the
> > following:
> >
> > When the table spans multiple pages, I want the textbox to print as
well.
> > The textbox is like header info for the table. I have a client with
> > invoices. The invoice data is shown in the table. The client name in
the
> > textbox.
> >
> > I've tried the RepeatWith property on the textbox and set the value to
the
> > name of the table, however, it still doesn't repeat. What am I
missing'
> >
> > Thx
> >
> >
>|||I understand. My valid email address is in my profile, so if you wouldn't
mind contacting me via email to discuss this further, please do.
Thx,
Russell
"Adrian Maull (MCP)" <no_spam@.no_email.org> wrote in message
news:exFpbU2xEHA.1400@.TK2MSFTNGP11.phx.gbl...
> No answer yet. I'm currently looking for other Report Services
> newsgroups.
> This one doesn't seem to be monitored very well.
> "Russell Hardie" <russell_hardie@.cranevalve.com> wrote in message
> news:%23ex51%23pxEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> So Adrian,
>> Did you ever get an answer or figure this issue out?
>> I could certainly use a solution.
>> Thx,
>> Russell
>> "Adrian Maull (MCP)" <no_spam@.no_email.org> wrote in message
>> news:eKAdWqZxEHA.3896@.TK2MSFTNGP10.phx.gbl...
>> > In the Body portion of a report I have a List. Within the List I have
>> > a
>> > textbox. Under the textbox I have a table with various columns. It's
>> > possible that the table spans more than one page, which is OK - it just
>> > depends on how much data I have. What I'm having trouble doing is the
>> > following:
>> >
>> > When the table spans multiple pages, I want the textbox to print as
> well.
>> > The textbox is like header info for the table. I have a client with
>> > invoices. The invoice data is shown in the table. The client name in
> the
>> > textbox.
>> >
>> > I've tried the RepeatWith property on the textbox and set the value to
> the
>> > name of the table, however, it still doesn't repeat. What am I
> missing'
>> >
>> > Thx
>> >
>> >
>>
>

How to make ship list

Dear all, I have a request to make a shipment list.
The shipment is listed by 3 columns in horizontal direction.
ex.
Original ship sn :
ship001
ship002
ship003
ship004
ship005
ship006
Ship List Report format :
ship001......ship002......ship003
ship004......ship005......ship006
How should I do in reporting services?
Should I use List, Table, or Matrix '!
please help!
thanks.You could use a List control and set the Layout\Columns property of the
report body to 3? (The Preview tab doesn't display this correctly though;
you will have it export it PDF to see it correctly).
Mark
"sam" <sam@.discussions.microsoft.com> wrote in message
news:A33C9698-2C6C-4BF8-A387-D6BCC2AACB81@.microsoft.com...
> Dear all, I have a request to make a shipment list.
> The shipment is listed by 3 columns in horizontal direction.
> ex.
> Original ship sn :
> ship001
> ship002
> ship003
> ship004
> ship005
> ship006
> Ship List Report format :
> ship001......ship002......ship003
> ship004......ship005......ship006
> How should I do in reporting services?
> Should I use List, Table, or Matrix '!
> please help!
> thanks.|||I have the same PB.
is it possible to have a multicolumn subreport ?
when i used nested multicolumn in my report, the other column are not display!
any idea '
regards
"MCC" wrote:
> You could use a List control and set the Layout\Columns property of the
> report body to 3? (The Preview tab doesn't display this correctly though;
> you will have it export it PDF to see it correctly).
> Mark
> "sam" <sam@.discussions.microsoft.com> wrote in message
> news:A33C9698-2C6C-4BF8-A387-D6BCC2AACB81@.microsoft.com...
> > Dear all, I have a request to make a shipment list.
> > The shipment is listed by 3 columns in horizontal direction.
> > ex.
> > Original ship sn :
> > ship001
> > ship002
> > ship003
> > ship004
> > ship005
> > ship006
> >
> > Ship List Report format :
> > ship001......ship002......ship003
> > ship004......ship005......ship006
> >
> > How should I do in reporting services?
> > Should I use List, Table, or Matrix '!
> > please help!
> > thanks.
>
>|||Another related question..
What if I want to place shipment header on the top of the page?
I cannot place any objects across the multi-columns report body.
Any suggestions or ideas for me ?
thanks
"MCC" wrote:
> You could use a List control and set the Layout\Columns property of the
> report body to 3? (The Preview tab doesn't display this correctly though;
> you will have it export it PDF to see it correctly).
> Mark
> "sam" <sam@.discussions.microsoft.com> wrote in message
> news:A33C9698-2C6C-4BF8-A387-D6BCC2AACB81@.microsoft.com...
> > Dear all, I have a request to make a shipment list.
> > The shipment is listed by 3 columns in horizontal direction.
> > ex.
> > Original ship sn :
> > ship001
> > ship002
> > ship003
> > ship004
> > ship005
> > ship006
> >
> > Ship List Report format :
> > ship001......ship002......ship003
> > ship004......ship005......ship006
> >
> > How should I do in reporting services?
> > Should I use List, Table, or Matrix '!
> > please help!
> > thanks.
>
>|||While in SSRS and the report is exported to PDF, the rows are not 3 across.
the records go down the top then down again.
"sam" wrote:
> Another related question..
> What if I want to place shipment header on the top of the page?
> I cannot place any objects across the multi-columns report body.
> Any suggestions or ideas for me ?
> thanks
> "MCC" wrote:
> > You could use a List control and set the Layout\Columns property of the
> > report body to 3? (The Preview tab doesn't display this correctly though;
> > you will have it export it PDF to see it correctly).
> >
> > Mark
> >
> > "sam" <sam@.discussions.microsoft.com> wrote in message
> > news:A33C9698-2C6C-4BF8-A387-D6BCC2AACB81@.microsoft.com...
> > > Dear all, I have a request to make a shipment list.
> > > The shipment is listed by 3 columns in horizontal direction.
> > > ex.
> > > Original ship sn :
> > > ship001
> > > ship002
> > > ship003
> > > ship004
> > > ship005
> > > ship006
> > >
> > > Ship List Report format :
> > > ship001......ship002......ship003
> > > ship004......ship005......ship006
> > >
> > > How should I do in reporting services?
> > > Should I use List, Table, or Matrix '!
> > > please help!
> > > thanks.
> >
> >
> >sql

How to make part of the report show in 2 columns?

I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?

John Smith

Title: MSTP Location: Main Campus IP:127.0.0.1

Softwares:

Adobe Standard 7.0 Access 5.0

Internet Explore 6.0 Office XP

Any suggestion is appreciated.

In Crystal Reports, there is an option called "format with multiple columns". Could not find the similar configuration in SQL reporting. Please help!

|||

HI, MiaF:

You can manualy select the two columns in the data section. Right click and select merge cells.

|||

Rex,

Can you explain a little bit more please? I wanted the results to show in multiple columns, how can I make it happen by merging cells? Also, when you say merging cells, you mean in a table?

I found this article that seems saying that what I asked is impossible:

http://msdn2.microsoft.com/en-us/library/ms155816.aspx

"A multi-column layout applies to the entire report. It is not possible to specify a multi-column layout on the top half of the report, and a tabular layout on the bottom half of the report. " Is there anyway to get around this? Thanks.

How to make more than One Page Report in Sql Server Reporting Services 2005

i have a stored procedure that return a single row and more than 100 colums data. i want to show that columns more than one page.

how can i design my report when i preview the report it shows the data on 2 page

DO you want to display the pages next to each other or do you want to display the second page under the first page ? For the first one simply extend the page size to the double size. For the second choice place multiple detail rows on the page until all data is displayed.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

How to make input columns unavailable for downstream components?

Hi,

In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.

Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...

Is that possible?

I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...

Thanks for any help,

David

David-Paris wrote:

Hi,

In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.

Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...

Is that possible?

I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...

Thanks for any help,

David

If the component you are building is a synchronous component then the rows will be available downstream. That is simply inherent in the nature of the data-flow.

Unpivot is an asynchronous component (just like Merge Join, Union All, Sort etc...). You can think of asynchronous as meaning that the "shape" of the data changes when it goes through the component (that's not really what it means but for simplicity - it works).

You can make your custom component asynchronous if you want but there isn't much point - it will degrade performance.

-Jamie

How to make indentity column continually?

Hello, everyone:

I have a table with an indentity column as first column. At beginning it is continue such as 0-50. I delete last 20 columns by hand. The 0-31 is left. When the new data is inserted, I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.

Thanks a lot

ZYTThis works for me. I'm using EM. After you delete the rows from the table, remove the IDENTITY property and save the change to the table. Then add the IDENTITY property back using the defaults (seed and increment of 1). The next row that will be added should have the next available number as it's IDENTITY value. In your case, 32.|||1. Add a new column
2. Copy data from identity column to new column
3. drop identity column
4. change name of new column to old identity column
5. make new column an identity

BUT!

The fact that you want to do this tells me the column should not be an identity column.

You will forever more be worry about gaps in sequences, ect.

The identity column shouldn't be used for "ordering" data.|||Wow, Brett makes laws...

DBCC CHECKIDENT('table_name', RESEED|NORESEED, <new_seed_value>)

And why can't you order by identity column?|||And why can't you order by identity column?

I guess that didn't come out right...

They are artificially leaning on an IDENTITY Column where gaps in the sequence is a problem...if they are building a process that is dependant on the fact that there has to be a "next row", then I would consider that a bad design.|||A surrogate key, whether INT or GUID, should not be relied upon for ordering data. By definition it has no inherent relationship to the data it represents.

It's not a law. It's a principle, and a good one.|||It's not the relationship to data that defines the ordering, it's the characteristics of the field. In this case if the key is clustered then the order is dictated by the value of the field, not by what data type it is or whether it has a relationship to data or not. READ THE POSTS!|||Dude, I can see your veins popping from here. That can't be healthy.

Yes, a clustered index is physically ordered. Duh.

But it is a bad idea to depend on a surrogate key not having gaps, or even being an indication of the order the data was created. That's what the datetime datatype is for.|||That's not what the topic was about, READ THE POSTS! And leave my veins alone. I am not saying anything about what's popping on your face, right? ;)|||I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.

Read the post...ok...

Dude...they run out of tequila in Texas?sql

How to make a safe varchar() to int conversion

I'm selecting a big group of records for output, and I need to convert a
couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
myintfield from ...)
Problem is, some erroneous data has non-numeric characters in it, and SQL
Server kills the whole SELECT, outputting no rows (!!!).
Is there any way to get SQL to just put NULL or 0 in for erroneous data -
the way you can use SET ARITHABORT to have it ignore numeric errors and keep
processing?
Thanks!
- NevynHello Nevyn,
One of the best way's I've found to do this is to use a LIKE clause in your
select statement
SELECT * FROM myTable WHERE numCol NOT LIKE '%[a-z]%'
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm selecting a big group of records for output, and I need to convert
> a couple columns from varchar to int. (SELECT CAST(mycharfield as int)
> as myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and
> SQL Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous
> data - the way you can use SET ARITHABORT to have it ignore numeric
> errors and keep processing?
> Thanks!
> - Nevyn
>|||You can just add
"where isnumeric(col)=1"
or
"where col like '%[^0-9]%'"
to your query
-oj
"Nevyn Twyll" <astian@.hotmail.com> wrote in message
news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...
> I'm selecting a big group of records for output, and I need to convert a
> couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
> myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and SQL
> Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous data -
> the way you can use SET ARITHABORT to have it ignore numeric errors and
> keep processing?
> Thanks!
> - Nevyn
>|||That would be "where col NOT like '%[^0-9]%'"
Gert-Jan
oj wrote:
> You can just add
> "where isnumeric(col)=1"
> or
> "where col like '%[^0-9]%'"
> to your query
> --
> -oj
> "Nevyn Twyll" <astian@.hotmail.com> wrote in message
> news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...|||Tks for the correction.
-oj
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42015A90.8863CB3B@.toomuchspamalready.nl...
> That would be "where col NOT like '%[^0-9]%'"
> Gert-Jan
>
> oj wrote:

How to make a DB to update its own Table colums

Is there any way to update a table columns automatically. For example:

We have a table(tblFirm) which columns are: FirmName,IsOpen
And another table(tblTime) which stores time information: OpenTime,CloseTime

What i want to do ; between the time intervals of OpenTime&CloseTime , DB will automatically set IsOpen's value to true, otherwise false. Is there anyway to do this?

Happy Coding...

If i am getting right, you want to Add a row in table tblFirm(with IsOpen as True/False), if the current time lies between OpenTime & CloaseTime ? if this is so, then you need to write a INSERT TRIGGRE on table tblTime, which will check the current time against the OpenTime & CloseTime, if time lies in between, then update the table tblTime with appropriate values for r FirmName & true/flase for the IsOpen.

Gurpreet S. Gill

|||

I agree partially with Gurpreet Singh Gill.

But it wont automatically set false when the current time elapsed with CloseTime.

The best solution is,
1. Create a function which will find the Firm is Open or Close from the TBLTIME table.

Create Function dbo.IsOpen(@.CurrentDate datetime, @.FirmId int) returns int
as
Begin
Declare @.IsOpen as int;
Select
@.IsOpen =
Case When @.CurrentDate <= Max(EndTime)
And @.CurrentDate >= Max(StartTime)
Then 0 Else 1 End
From
TBLTIME
Where
FirmId = @.FirmId;

Return @.IsOpen;
End

2. Change the IsOpen column of your table as Computed Column

create table TBLFIRM
(
FirmId int,
IsOpen as dbo.IsOpen(Getdate(),FirmId)
)

|||

ManiD, this seem to be the good solution. There is always a number of solution for a given problem, specially the filed in which we are.

Gurpreet S. Gill

|||

ManiD thanks for helps. Also thanks Gurpreet Singh Gill for post. Solutions ar great...

Happy Coding...

How to make a DB to update its own Table colums

Is there any way to update a table columns automatically. For example:

We have a table(tblFirm) which columns are: FirmName,IsOpen
And another table(tblTime) which stores time information: OpenTime,CloseTime

What i want to do ; between the time intervals of OpenTime&CloseTime , DB will automatically set IsOpen's value to true, otherwise false. Is there anyway to do this?

Happy Coding...

If i am getting right, you want to Add a row in table tblFirm(with IsOpen as True/False), if the current time lies between OpenTime & CloaseTime ? if this is so, then you need to write a INSERT TRIGGRE on table tblTime, which will check the current time against the OpenTime & CloseTime, if time lies in between, then update the table tblTime with appropriate values for r FirmName & true/flase for the IsOpen.

Gurpreet S. Gill

|||

I agree partially with Gurpreet Singh Gill.

But it wont automatically set false when the current time elapsed with CloseTime.

The best solution is,
1. Create a function which will find the Firm is Open or Close from the TBLTIME table.

Create Function dbo.IsOpen(@.CurrentDate datetime, @.FirmId int) returns int
as
Begin
Declare @.IsOpen as int;
Select
@.IsOpen =
Case When @.CurrentDate <= Max(EndTime)
And @.CurrentDate >= Max(StartTime)
Then 0 Else 1 End
From
TBLTIME
Where
FirmId = @.FirmId;

Return @.IsOpen;
End

2. Change the IsOpen column of your table as Computed Column

create table TBLFIRM
(
FirmId int,
IsOpen as dbo.IsOpen(Getdate(),FirmId)
)

|||

ManiD, this seem to be the good solution. There is always a number of solution for a given problem, specially the filed in which we are.

Gurpreet S. Gill

|||

ManiD thanks for helps. Also thanks Gurpreet Singh Gill for post. Solutions ar great...

Happy Coding...

How to make 2 columns in datagrid with random records?

Hi
I want to make random record from both columns
Not just like this
"SELECT Firstname,Lasename FROM rndnames ORDER BY NewID()"
but more like this but this code dont work becouse i dont know how to put it into the code:
"SELECT Firstname FROM rndnames ORDER BY NewID()"
"SELECT Lasename FROM rndnames ORDER BY NewID()"
As you see i want both columns to be random placed..
Please help me...
Well i found one way that was 2 tables the code ended like this
SqlConnection1.Open()
Dim sqlcon As NewSqlCommand("Select Firstname,spillernavn From rndnames CROSS JOINspillere ORDER BY NewID()", SqlConnection1)
Dim sqlrd As SqlDataReader
sqlrd = sqlcon.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataSource = sqlrd
DataGrid1.DataBind()
Well Well at least it works;)

How to Limit the number of columns in the Matrix ?

HI all !

I am having a bit of a problem trying to limit a number of columns in a matrix appearing on a page.

At the moment, I have a dataset that lists the month and the mail packages that were sent during the month
The matrix works great HOWEVER, if there were more than 8 months in the matrix columns, it does not break and would make the page look like a huge landscape page.

I am trying to limit the number of columns appearing (this is the months column) on the matrix so that the pages stay in a potrait position. IE: every 8 columns appear on one page. Is there an option or an expression I could use in the Matrix ?
Thanks!

BErnard Ong

An update on this situation.

After figuring what keywords to use, I think I might have found the answer to this dilemma.

This is the post that might be useful to anyone who might want to wrap the number of columns in the matrix so that it will print a new page !

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=934445&SiteID=1

Can anyone suggest what would be a better way to title my thread so that other users can find this easily if they have this problem ? never thought of using the wrap keyword, and I actually had to find this thread by using "Columns in Matrix" and searching through a couple of pages.

Thanks !

Bernard Ong

how to knw which column is primary key in a table

hi all

my question is which query shud i use in sql server 2000 to get which column or columns are primary keys of table

i dont want to use any stored procedures only sql query

sp_primary_keys_rowset is one of d stored proc in sql server 2005 but i couldn't understand which query they are using

i only want to use sql query

select o.name as TableName,
c.name as ColumnName
from sysindexes i
inner join sysobjects o ON i.id = o.id and o.xtype='U'
inner join sysobjects o2 ON i.name = o2.name
and o2.parent_obj = i.id
and o2.xtype = 'PK'
inner join sysindexkeys i2 on i.id = i2.id
and i.indid = i2.indid
inner join syscolumns c ON i2.id = c.id
and i2.colid = c.colid
order by o.name,i2.keyno

|||

It is easier to use the information_schema view key_column_usage and the objectproperty function:

select table_schema + '.' + table_name as table_name, column_name
from information_schema.key_column_usage
where objectproperty(object_id(constraint_name),'IsPrimaryKey') = 1
order by table_schema, table_name