rows as columns

is it possible to write a query so that we can have all rows of one column in a single column
TIA
Yes. You could concatenate all the rows in the query as:
SELECT
column1 + column2 + column3
FROM
yourtable
One thing to note here is, since the columns would have different datatypes if you try to concatenate varchar column with int column SQL Server might throw an error. So it is adviced to use CONVERT function to convert all the values into varchar, something like:
SELECT
( CONVERT(varchar(5),intcolumn1) + CONVERT(varchar(10),decimalcolumn2) + regularvarcharcolumn3 )
FROM
yourtable

Row Lock On Update Statement

What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,
You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>

Row Lock On Update Statement

What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>

Row Lock On Update Statement

What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>

Row count on all tables in the database

Does anyone have a single T-SQL script that could be run against a database that would return the table name and row count for each table?

Code Snippet

declare @.cmd nvarchar(max)

set @.cmd=null

select @.cmd =coalesce(@.cmd +'; ','')+

N'SELECT COUNT(*) AS "'+

quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)+

N' Count" FROM '+quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)

frominformation_schema.tables

execsp_executesql @.cmd

|||

Alternate variation:

Code Snippet

declare @.cmd nvarchar(max)

set @.cmd=null

select @.cmd =coalesce(@.cmd +' union all ','')+

N'SELECT '''+

quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)+

N''' AS TableName, COUNT(*) AS "Rows" '+

N' FROM '+quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)

frominformation_schema.tables

execsp_executesql @.cmd

|||Thanks for the reply Dale. I get results when running this against my master database but not against my DSS database (which is the one I am really after). Is there a variation that would work for my DSS database?|||

The INFORMATION_SCHEMA.TABLES runs against the current database.

Issue USE DSS; in front of the rest of the code.

|||

A fair estimate can be get from:

-- 2000

use your_db

go

dbcc updateusage (0) withcount_rows

go

select

object_name([id]),

rowcnt

from

sysindexes

where

indid in(0, 1)

andobjectproperty([id],'IsUserTable')= 1

andobjectproperty([id],'IsMSShipped')= 0

go

-- 2005

select

object_name([object_id]),

sum([rows])as rowcnt

from

sys.partitions

where

objectproperty([object_id],'IsUserTable')= 1

andobjectproperty([object_id],'IsMSShipped')= 0

groupby

[object_id]

go

AMB

Rotation of Column Headings in a Grid Report

Is there any way to specify that you want a column heading rotated to 45 or 90 degrees? I have a grid report with single character values in the grid but the column headings are very lengthy so the resulting report is several pages wide. I'd like to be able to rotate the column headings 90 degrees (this is easily done in Excel) to get the report to fit on a single page.Change the textbox WritingMode property to tb-rl. Sorry, no tb-lr yet.