How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchanan
I figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanansql

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan

How can i reset the Primary key field numbers to zero before uploading my site to the prod

I have been testing my database application for a while now. As a result i have a lot of records already with their respective identity field values. What i want to do is to reset this field to 0 (zero) before i deploy the aplication. I have tried deleting all the records and entered one to see if the id will be 1 but it still starts from where it was before i deleted the records. Any ideas.DBCC CHECKIDENT (tableName, RESEED, 0)|||

usingtruncate tablewill help you.

thanks,

satish.

sql

How can I report on entered and updated records

My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table.

Maybe a tool someone knows of?

Jackxxx:

My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table.

Maybe a tool someone knows of?

You mean without doing a SELECT on the tables? Check out some log reader tools - Red Gate has one.. perhaps there are others..but then they only have so many transactions and not your entire database..

How can I query this on linked servers...

For example I have 15 records in which accountID is a PK. AccountID 1,2,3,4,5 is linked on server1. I want to find out records that are not linked. So its record 6 - 15. Can someone show the stored procs for this scenario? Or the query statement to do this... Im new to linked servers. Thanks alot.

The only difference would be the way you do your SELECT. you need to include the 4-part naming convention: server.Database.schemaowner.objectname

How can I programatically delete records in tables...

Ok,
In my c# app, i have a string with an invoice number: "str = 12345".
Using sqlserver2k:
first of all, i understand the c# requirments for putting a command
string in, but don't know the sql command.
this is what i need:
tblPrintInvoice has one record with this column_invoice_number#.
tblInvoiceDetails has maybe up to 50.
How can I delete str(12345) in both tables most efficiently?
Also, i may need to put a flag in each record instead of deleting
them...like:
column_invoice_flag for all 12345 records gets an "insert?" of (reprint
- don't delete) instead of deleting.
any help is appreciated.
thanks,
trintHere are two examples:
DELETE FROM tblInvoiceDetails
WHERE invoice_number = '12345'
UPDATE tblInvoiceDetails
SET deleted = 'Y'
WHERE invoice_number = '12345'
Lookup the DELETE and UPDATE statements in Books Online for the full
syntax.
David Portas
SQL Server MVP
--

How Can I obtain the Top 3 of a dataset

If I have say 20 records of sales employees, for example, how can I get the top 3 locations for $$$sales for EACH employee? Each employee can have multiple locations where they have sold(let's say up to 50). I only want the names of the top 3 locations. The closest I can get is filtering the dataset by a HAVING clause > a dollar amount but this still gives me between 3 - 12 records for each plus I have to literally enter each salesperson's number as it stands now. Is this a loop or a cursor? Thanks.

ddaveOnce you have your SQL set up to return the sales dollars for all of the locations, just add TOP 3 before the select:

SELECT TOP 3 Sum(Dollars) FROM MyTable;


Note that you can also SELECT TOP n PERCENT.|||General solution to your problem:

The TOP N clause in TSQL is useful, but is limited by the fact that it only applies to the entire recordset, and that it will not accept a variable as a parameter.

This method will return any requested number of records for each group of one or more column values in a dataset. It uses a type of join called a Theta join, where the values in two datasets are compared, but do not necessarily have to be equal:


declare @.N int
set @.N = 5 --The number of records to return for each grouping.
select YourTable.YourColumns
from YourTable
inner join YourTable ThetaTable
on YourTable.GroupColumns = ThetaTable.GroupColumns
and YourTable.SortColumn <= ThetaTable.SortColumn
group by YourTable.YourColumns
having count(*) <= @.N|||The TOP function does not work as blindman indicates as it gives me only the top 3 of 120+ records, not the top 3 for EACH person. I will try the Theta join. Thanks to both for replying.

ddave|||blindman, your queries are starting to look good :)

but the join should be outdented

use foo and bar if more than one column is intended (YourColumns alone does not show comma syntax)

and shorter aliases make it easier to read ;)select T.foo, T.bar
from YourTable as T
inner join YourTable as Th
on T.GroupColumns = Th.GroupColumns
and T.SortColumn <= Th.SortColumn
group by T.foo, T.bar
having count(*) <= n|||blindman, your queries are starting to look good :)

Looks can be deceiving...it doesn't work

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(SiteId int, EmpId int, Sales money)
GO

INSERT INTO myTable99(SiteId, EmpId, Sales)
SELECT 1, 1, 10.00 UNION ALL
SELECT 2, 1, 15.00 UNION ALL
SELECT 3, 1, 20.00 UNION ALL
SELECT 4, 1, 50.00 UNION ALL
SELECT 5, 1, 10.00 UNION ALL
SELECT 6, 1, 5.00 UNION ALL
SELECT 1, 2, 100.00 UNION ALL
SELECT 2, 2, 1500.00 UNION ALL
SELECT 3, 2, 2000.00 UNION ALL
SELECT 4, 2, 5000.00 UNION ALL
SELECT 5, 2, 1000.00 UNION ALL
SELECT 6, 2, 500.00 UNION ALL
SELECT 1, 3, 1.00 UNION ALL
SELECT 2, 3, 1.50 UNION ALL
SELECT 3, 3, 2.00 UNION ALL
SELECT 4, 3, 5.00 UNION ALL
SELECT 5, 3, 1.00 UNION ALL
SELECT 6, 3, .50
GO

DECLARE @.N int
SET @.N = 3 --The number of records to return for each grouping.

SELECT a.SiteId, a.EmpId
FROM myTable99 a
JOIN myTable99 b
ON a.SiteId = b.SiteId
AND a.EmpId = b.EmpId
AND a.Sales < = b.Sales
GROUP BY a.SiteId, a.EmpId
HAVING COUNT(*) <= @.N
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

I know I've seen this work somehow though|||At least to me, it is a lot more intuitively obvious to do this via a sub-query, something like: DECLARE @.N int
SET @.N = 3 --The number of records to return for each grouping.

SELECT a.SiteId, a.EmpId, a.Sales
FROM myTable99 a
WHERE (SELECT Count(*)
FROM myTable99 b
WHERE b.EmpId = a.EmpId
AND a.Sales <= b.Sales) <= @.N
ORDER BY a.EmpID, a.Sales DESC, a.SiteIDThe problem with any set-based solution to this kind of problem is that it does not deal well with "ties" in the data... They make your results a bit "funky", but I don't know any reliable way to resolve that using set based logic.

-PatP|||I know I've seen this work somehow thoughyeah, it's tricky

here's what you were searching for -- select a.EmpId
, a.SiteId
, a.sales
from myTable99 a
inner
join myTable99 b
on a.EmpId = b.EmpId
and a.Sales <= b.Sales
group
by a.EmpId
, a.SiteId
, a.sales
having count(*) <= 3
order
by a.EmpId
, a.sales descwhich produces the following (correct) results:1 4 50.0000
1 3 20.0000
1 2 15.0000

2 4 5000.0000
2 3 2000.0000
2 2 1500.0000

3 4 5.0000
3 3 2.0000
3 2 1.5000pat, i like the subquery method too, i guess i just got used to the join solution after so many times showing pre-4.1 mysql people how to do it

:) :)|||I indents 'em as I wants 'em, thank you! :)|||Hey thanks guys...

And yes Pat, I agree it may be more intuitive...but have a look at the plans...it looks like Rudy's Join is more effecient...even with the group by.

I would not have guessed this. Anyone care if I blog this? With appropriate references of course.

It's just one of things I know can be done...and I forget how to contruct it.

Anyway, here's the plans|||blog away

:cool: :cool: :cool:|||OK, You've all been Blogged (http://weblogs.sqlteam.com/brettk/archive/2005/02/10/4153.aspx)|||OK, You've all been Blogged (http://weblogs.sqlteam.com/brettk/archive/2005/02/10/4153.aspx)ta very much

and usually i just ignore your frequent typos, but in this case, i must insist that you correct the spelling of my surname

thanks in advance|||mia culpa...done|||mia culpa...donethanks

and now, would you mind changing the link so that it points to the correct person

:) :)|||Done...it's been a rough week...they've got me doing project plans...aaaahhhhhhhhh|||"The problem with any set-based solution to this kind of problem is that it does not deal well with "ties" in the data... They make your results a bit "funky", but I don't know any reliable way to resolve that using set based logic."

That is the catching point. I have 20 people, for each of whom I wish to get the "TOP 3" records. When I have a "tie" it fails, giving me only 2 of the 3 desired records.

I inserted a couple of extra records to the table given above by:

INSERT INTO myTable99(SiteId, EmpId, Sales)
SELECT 2, 2, 15.00
SELECT 2, 2, 1500.00

The 1500.00 will create a tie with another record, already having a value of 1500.00.

Here are all the records:

select *
from myTable99

Results:
1 1 10.0000
2 1 15.0000
3 1 20.0000
4 1 50.0000
5 1 10.0000
6 1 5.0000
1 2 100.0000
2 2 1500.0000
3 2 2000.0000
4 2 5000.0000
5 2 1000.0000
6 2 500.0000
1 3 1.0000
2 3 1.5000
3 3 2.0000
4 3 5.0000
5 3 1.0000
6 3 .5000
2 2 1500.0000
2 2 15.0000

When I run your query:

select a.EmpId
, a.SiteId
, a.sales
from myTable99 a
inner
join myTable99 b
on a.EmpId = b.EmpId
and a.Sales <= b.Sales
group
by a.EmpId
, a.SiteId
, a.sales
having count(*) <= 3
order
by a.EmpId
, a.sales desc

My results are here:

1 4 50.0000
1 3 20.0000
1 2 15.0000
2 4 5000.0000
2 3 2000.0000
3 4 5.0000
3 3 2.0000
3 2 1.5000

I need Employee 2 to show at least one of his two 1500.00 records. Any way to do this?

ddave|||This can still be done with a set-based solution:

select a.EmpId,
a.SiteId,
a.sales
from myTable99 a
inner join myTable99 b
on a.EmpId = b.EmpId
and (a.Sales < b.Sales
or a.Sales = b.Sales and a.EmpID < b.EmpID)
group by a.EmpId,
a.SiteId,
a.sales
having count(*) <= 3
order by a.EmpId,
a.sales desc

Output:

EmpId SiteId sales
---- ---- -------
1 3 20.0000
1 2 15.0000
1 1 10.0000
1 5 10.0000
2 3 2000.0000
2 2 1500.0000
2 5 1000.0000
3 3 2.0000
3 2 1.5000
3 1 1.0000
3 5 1.0000|||No kidding...

SELECT a.EmpId, a.SiteId, a.sales
FROM (SELECT DISTINCT EmpId, SiteId, sales FROM myTable99) a
INNER JOIN (SELECT DISTINCT EmpId, SiteId, sales FROM myTable99) b
ON a.EmpId = b.EmpId
AND a.Sales <= b.Sales
GROUP BY a.EmpId, a.SiteId, a.sales
HAVING COUNT(*) <= @.N
ORDER BY a.EmpId, a.sales desc
GO|||without getting into the actual sql, the problem of ties isn't really a problem of sql, it's more of a problem of semantics

blindman's last example is perfect --

3 3 2.0000
3 2 1.5000
3 1 1.0000
3 5 1.0000

as far as i'm concerned, a tie across the last place requires that all rows with that value be included

let's say we had 25 people in a classroom, and we wanted the top 3 students based on marks

joe has a gpa of 3.90, mary has a gpa of 3.85, and all twenty-three others have exactly the same gpa, 3.80

who ya gonna exclude?

How Can i Now the RecordSource number of records?

Hi...

I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn′t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ?How i can khow the number of records of a RecordSource gets?... and ?How I can store in a variable a field value retrived for a RecordSource?... I′m using VWD and SQL Express that means ADO.NET 2.0...

cheers.

How are you binding to your datasource? A SqlDataSource, ObjectDataSource, or are you binding to the datasource in your code using a DataTable, DataReader, or DataSet?

How can I make this update statement faster

Does anyone know how to make this Update statement run faster? Currently it
takes over 30 minutes; there are over 4,310,000 records that would be
updated. Thanks for any suggestions.
UPDATE ParcelAuthority
SET PrevDue = lastYear.Due
FROM
(
SELECT PrcNr, AuthCd, Due
FROM ParcelAuthority
WHERE YearNr = 2004
) lastYear
WHERE ParcelAuthority.PrcNr = lastYear.PrcNr
AND ParcelAuthority.AuthCd = lastYear.AuthCd
AND ParcelAuthority.YearNr = 2005create an index on year,PrcNr, AuthCd in that order. if one does not exist
and
try to rephrase the query as
UPDATE A
SET PrevDue = B.Due
FROM ParcelAuthority a, ParcelAuthority b
WHERE A.PrcNr = B.PrcNr
AND A.AuthCd = B.AuthCd
AND A.YearNr = 2005
and B.YearNr = 2004|||Do you mean create non clustered indexes as follows:
CREATE NONCLUSTERED INDEX ParcelAuthority_YearNr
ON ParcelAuthority (TaxYearNr)
CREATE NONCLUSTERED INDEX ParcelAuthority_PrcNr
ON ParcelAuthority (PrcNr)
CREATE NONCLUSTERED INDEX ParcelAuthority_AuthCd
ON ParcelAuthority (AuthCd)
"Omnibuzz" wrote:

> create an index on year,PrcNr, AuthCd in that order. if one does not exist
> and
> try to rephrase the query as
> UPDATE A
> SET PrevDue = B.Due
> FROM ParcelAuthority a, ParcelAuthority b
> WHERE A.PrcNr = B.PrcNr
> AND A.AuthCd = B.AuthCd
> AND A.YearNr = 2005
> and B.YearNr = 2004
>|||well if you already have clustered index and not on these columns in this
order,
create the non clustered index this way.
Create Procedure stp_test
CREATE NONCLUSTERED INDEX IdxParcelAuthority
ON ParcelAuthority (TaxYearNr,PrcNr,AuthCd)|||On Wed, 19 Apr 2006 11:36:02 -0700, Omnibuzz wrote:

>well if you already have clustered index and not on these columns in this
>order,
>create the non clustered index this way.
>Create Procedure stp_test
>CREATE NONCLUSTERED INDEX IdxParcelAuthority
> ON ParcelAuthority (TaxYearNr,PrcNr,AuthCd)
Hi Omnibuzz,
If Petro adds Due to this nonclustered index, it will become a coovering
index. This should speed up the query even further.
Hugo Kornelis, SQL Server MVP|||
> Hi Omnibuzz,
> If Petro adds Due to this nonclustered index, it will become a coovering
> index. This should speed up the query even further.
Perfect Hugo. Yeah... why didn't I think of it :) and to extend hugo's
comment, if its in SQL Server 2005 we can add Due as a non-key column in the
index.

How can I know who inserted a certain record?

Dears,
I have either a service or even a user who keeps adding records to a certain table. Is there any way to know who added the records? Other than using the SQL Profiler.
Thanks,To collect statistical information about users, use either SQL Profiler or System Monitor (Windows NT Performance Monitor in Windows NT 4.0). Use the SQL Server Enterprise Manager Current Activity window to perform ad hoc monitoring of SQL Server, which allows you to determine user activity on the system.

Also, type this into the Books Online Search "Monitoring with SQL Server Enterprise Manager"

Also, is there a lock for the INSERT?

Hope it helps...|||Just add a column with as default User

How can I know the records in SQL 2005 Table possess size space?

We can know easily a database disk size, but can we konw a table in SQL 2005 database possess size? and more, how can I know the records in SQL 2005 Table possess size space?

Try the link below for sp_helpfile it is a system stored procedure and it is in the Master database. Hope this helps.

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

|||

Thanks!

But I think Sp_helpfile don't know single Table or Records size

sp_helpfile (Transact-SQL)

Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server.

|||

Sorry it is sp_spaceused and there are undocumented system stored proc that you can use. Try the links below for details.

http://www.sqlservercentral.com/columnists/achigrik/sql2000.asp

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1062

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

|||

Many thanks!

How can I know rows used space ? Such as I want to know the user Paul used space

select * from mytable where UserName='Paul'

|||Paul spaced used? Paul plus tables associated to Paul because sp_spaceused is for database objects like tables not users.|||

Sorry!

When system perform the sql select * from mytable where UserName='Paul' , it will return many rows , iI want to know these rows use size space.

|||

I am sorry I cannot help you the space used that concerns my employers are to make sure the transaction log will not run out of space, but I know the max for a row is 8060 which is also a SQL Server page. Try the thread below for the specs per Microsoft.

http://forums.asp.net/thread/1398414.aspx

|||

ok just open enterprise manager select the database then in the summary window just click report then expand disk space used by tables and you should get the info you're looking for as per table.

Have fun!

How can i import records from a sql server table in project1 to another sql server table i

Hi, i have a table with all employee bio-data in a completed project. Iam now working on another project with a table that needs the same data and here iam talking about 300 records that rarely change. Instead of re-entering this data in this new table, i want to import the data from the completed project into a table in this new project. Does any one have any idea how to achieve that or is there a better option to do the same.

One more thing iam realising here is that iam going to use this same data in very many applications and some one from one department is going to enter this data in all these different applications. so i was wondering if there could be a way of having a central database that this guy can mantain and then i be able to use that table data in different applications that iam going to develop. I dont want to kill this unlucky guy with data entry tasks every time i deploy a new application.

So basically, how can make one database application sever several different applications with its data.

Why hold the database within the project at all? Move the database to the server, create one account for updating and one other applications that just need to read the data.

|||

You can share database for different projects. You can copy database tables from one database to another. Here is a sample for you to move one table from one database to another database without creating the new table first.

Run this from the database you want to export data:

SELECT*INTO newDataBaseName.dbo.yourNewTableFROM tableToExpost

|||

TATWORTH:

Why hold the database within the project at all? Move the database to the server, create one account for updating and one other applications that just need to read the data.

Iam running sql sever express 2005 with advanced services and visual web developer 2005 express edition. I also got info some where on msdn that sql sever express does not support multiple connections to a .mdf file. If this is true, then wont i run into error like

1. A connection can not be made, the file is being used by a nother process or application.

2. The database is closed.

|||

From http://msdn2.microsoft.com/en-us/library/ms345154.aspx#sseover_topic19

QL Server Express 2005MSDE 2000User Instance supportFeature not presentDTS in separate downloadDTS runtime presentEasy deployment because of no MDACMDAC is part of installMSI only, good servicing storyMSI and MSM, servicing of MSM hardSubscriber Replication for Transactional, Merge, and snapshotMerge/snapshot publication supported in addition to replication subscriptionRobust setup UIBasic setup UINo agentAgent presentSupports Windows 2000 SP4, Windows XP SP1, and Windows 2003Supports Windows 98, Windows Me, Windows 2000, Windows XP, Windows NT4, and Windows 2003CLR supportNo CLR supportGUI tools availableNo GUI toolsDatabase size limit: 4 GBDatabase size limit: 2 GB1 CPU, 1 GB RAM2 CPU, 2 GB RAMNo throttleWorkload throttle enforced for 5 concurrent workloadsDeep integration with Visual StudioBasic integration with Visual Studio

The phrase "No Throttle" implies that a given database can be used by more than one process.

How Can I get my sproc to return records with 0 as ClientID

Can anyone help me modify this sproc's Where clause or Joins to let (T) task records with a 0 to be returned?

If I enter a ClientID I want to return only those task records with the ClientID I entered (this works).

If I enter no ClientID I want to return all task records, even those with a 0 in the ClientID field.

ALTER PROCEDURE dbo.CMAdmin

@.SID int

AS
SELECT
A.CompanyName,
C.FirstName,
C.LastName,
C.ClientID,Convert(varchar(10),
T.ActionDate, 10) AS [Action Date],
T.Priority,
T.Status,
T.Subject,
T.Note,
T.CompletionDate, 10) AS Completed,
T.DateEntered AS Entered,
T.EnteredBy AS [Entered By],

CASE
WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN
C.[FirstName] +' '+ C.[LastName]
ELSE A.[CompanyName]
END AS DRName

FROM
tblClients C LEFT OUTER JOIN tblClientAddresses A
ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T
ON C.ClientID = T.ClientID

WHERE
C.ClientID = Isnull(@.SID,C.ClientID)

hi

u can modify the where close like this

ALTER PROCEDURE dbo.CMAdmin

@.SID int

AS
SELECT
A.CompanyName,
C.FirstName,
C.LastName,
C.ClientID,Convert(varchar(10),
T.ActionDate, 10) AS [Action Date],
T.Priority,
T.Status,
T.Subject,
T.Note,
T.CompletionDate, 10) AS Completed,
T.DateEntered AS Entered,
T.EnteredBy AS [Entered By],

CASE
WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN
C.[FirstName] +' '+ C.[LastName]
ELSE A.[CompanyName]
END AS DRName

FROM
tblClients C LEFT OUTER JOIN tblClientAddresses A
ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T
ON C.ClientID = T.ClientID

WHERE
C.ClientID = COALESCE(@.SID,C.ClientID)

this query will return all records when client id is null and when it is not null it only returns rows specified in the where condition

for more details

http://www.sqlteam.com/item.asp?ItemID=2077

regards

How can I get Actual Operation Cost from Actual Execution Plan?

I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.

Thanks!

Henry

There is only Estimated Operation Cost, since the actual operation cost will change from time to time. You may use DBCC to rearange the page allocation, to see if the performance can be improved. However, the view tooks 30 sec. to run does not look good. I would check the view first, to see if there is any thing wrong.|||

Thank you for the response. Because the view is using in production site, we are not able to do tuning on it. We need to figure out why there are big different after deleted records and explain to client. Do you know any way to show us which step or operation spent more time than before. Thanks!!

How can I get a count of all records in each table in my DB?

Hoping you can help me here... I have a situation where I need to provide
a
script that can be run through query analyzer to return the number of record
s
in each table in my DB.
I would like it to be dynamic, as the number of tables in the DB changes.
Pretty simple query (Select count(*) from tblXXX), but I can't seem to find
where the actual table names are stored within the system tables so that I
can run through all of them.
I know that I could use Enterprise Manager and view the DB as a TaskPad to
see the number of records in each table, but the person who will be doing
this only has permissions to view the DB through QA.
Any thoughts on a script that will do this?
Thanks in advance...
gTry,
use northwind
go
-- undocumented sp
exec sp_msforeachtable 'select ''?'', count(*) from ?'
go
-- using sysindexes
dbcc updateusage (0)
select object_name([id]), rowcnt
from sysindexes
where indid in (0, 1) and objectproperty([id], 'IsUserTable') = 1
order by 1
go
-- using a cursor and dyanmic sql
create table #t (
table_name sysname,
rowcnt int,
)
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare my_cursor cursor
local
fast_forward
for
select
table_schema,
table_name
from
information_schema.tables
where
table_type = 'BASE TABLE'
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + ''' as table_name, count(*) as rowcnt from
[' + @.ts + '].[' + @.tn + ']'
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t order by table_name
drop table #t
go
AMB
"Greg Toronto" wrote:

> Hoping you can help me here... I have a situation where I need to provid
e a
> script that can be run through query analyzer to return the number of reco
rds
> in each table in my DB.
> I would like it to be dynamic, as the number of tables in the DB changes.
> Pretty simple query (Select count(*) from tblXXX), but I can't seem to fin
d
> where the actual table names are stored within the system tables so that I
> can run through all of them.
> I know that I could use Enterprise Manager and view the DB as a TaskPad to
> see the number of records in each table, but the person who will be doing
> this only has permissions to view the DB through QA.
> Any thoughts on a script that will do this?
> Thanks in advance...
> g|||In SQL Server 2000:
DBCC UPDATEUSAGE(0);
select o.name, i.rowcnt
from sysobjects o
inner join sysindexes i
on o.id = i.id
WHERE i.indid IN (0,1)
AND OBJECTPROPERTY(o.id, 'IsMsShipped') = 0
ORDER BY o.name
In SQL Server 2005, it's a little more complex, accounting for table
partitions:
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Greg Toronto" <GregToronto@.discussions.microsoft.com> wrote in message
news:1AEB569D-DA79-4B7B-9892-932B37D4823E@.microsoft.com...
> Hoping you can help me here... I have a situation where I need to
> provide a
> script that can be run through query analyzer to return the number of
> records
> in each table in my DB.
> I would like it to be dynamic, as the number of tables in the DB changes.
> Pretty simple query (Select count(*) from tblXXX), but I can't seem to
> find
> where the actual table names are stored within the system tables so that I
> can run through all of them.
> I know that I could use Enterprise Manager and view the DB as a TaskPad to
> see the number of records in each table, but the person who will be doing
> this only has permissions to view the DB through QA.
> Any thoughts on a script that will do this?
> Thanks in advance...
> g

How can I generate variables automatically?

Hi there

I need to generate some variables , like var1, var2, var3 ...

It's determined by how many records in my database.

I want to write a for() to do this. But how?

For example

for(int i=0; i<datatable.rows.count; i++)
{
String vari = new String;
}

This is not a right way in C#, does anyone know how to?

Thanks!!!

int _intTotalRowCount=0;

_intTotalRowCount=DataBaseTable.RowsCount;

string _strVar ="";for (int _intCount = 0; _intCount <_intTotalRowCount; _intCount++)

{

_strVar = _strVar+","+ "Var" +_intCount.ToString();

}

Label1.Text= _strVar;

How can I find records of a table which inserted today(for example)

Hi
My question is How can i find records of a table which insterted today(for
example) or after a specified time.
Thanks a lotA double post...
Please see response to previous question...
HTH. Ryan
"Hayatbakhsh" <hayatbakhsh@.gmail.com> wrote in message
news:upFAe6aIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> My question is How can i find records of a table which insterted today(for
> example) or after a specified time.
> Thanks a lot
>