March 25, 2008 at 4:50 am
Dear Experts,
I have written the following code to retrieve the record count of all the tables from a DB.
--------
Create table #Tables
(
ID numeric identity,
name varchar(75)
)
Create Table #FinalResult
(
TableName varchar(75),
RecordCount int
)
insert into #Tables
Select name From sysobjects
Where type ='U'
Declare @Start int,
@MaxCnt int,
@sql varchar(255)
Select @Start = 1
Select @MaxCnt = count(name) from #Tables
while @Start <= @MaxCnt
Begin
select @sql = 'insert into #FinalResult select "'+name+'",ct = (select count(*) from ' + name +')'
from #Tables
where ID = @Start
print @sql
execute (@Sql)
select @Start = @Start + 1
End
select * from #FinalResult
------------
My Questions:
1) How to avoid While Loop ?
2) I dont want to use Temp tables or Temp variables.How to achieve this without temp tables?
3) Can anybody help me to re write this code without using While loop ?
karthik
March 25, 2008 at 5:27 am
If you want to run a count(*) on all your tables, that's pretty much the way to do it. You could use a cursor instead of a while loop, or the sp_MSforeachtable proc, but behind the scenes they're all looping over the tables.
If you're using SQL 2000 and you don't need an exact count, you could query sysindexes and retrieve the rows column for all indexes where the indid is 0 or 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2008 at 5:39 am
----------------------------------------
You could use a cursor instead of a while loop
------------------------------------------
Can you tell me why ?
I am using sql2000. But most of the tables doesn't created with proper index.Thats why i haven't used sysindexes table.
karthik
March 25, 2008 at 5:43 am
Go thru this article:
http://www.sqlservercentral.com/scripts/Administration/61766/
Also go thru the dicussion forum on this article. There are couple of solutions which work fine.:P
March 25, 2008 at 5:48 am
karthikeyan (3/25/2008)
------------------------------------------Can you tell me why ?
Because it's an alternative to the while loop as you're written it. I'm not saying it will be better or worse in terms of time or resources. Try it and see.
I am using sql2000. But most of the tables doesn't created with proper index.Thats why i haven't used sysindexes table.
Doesn't matter. All tables will have an entry in sysindexes whether or not thay have any indexes on them. That's why I said filter sysindexes for indID of 0 or 1. 0 is for a heap, 1 for a cluster. Beteween the two, you'll have covered all the tables
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2008 at 7:17 am
Gila,
As you suggest i used sysindexes table.
Code:
select Name,Count = RowCnt
from sysobjects a,sysindexes b
where a.id = b.id
and a.type = 'U'
and a.name not in ('dtproperties')
It works fine in sqlserver2000.But i will have to use the same code in sybase also. I have checked out sysindexes table in sybase.It doesn't have RowCnt column.
Can you suggest some idea to modify the code which i posted very first ?
karthik
March 26, 2008 at 3:07 am
Any inputs ?
karthik
March 26, 2008 at 5:17 am
I have used the following code to find out the columns and Datatypes of all the tables for a DB.
Code:
-------------------------
Create table #Tables
(
ID numeric identity,
name varchar(75)
)
Create Table #FinalResult
(
TableName varchar(75),
ColumnName varchar(50),
DataType varchar(50) Null,
Length int
)
insert into #Tables
Select name From sysobjects
Where type ='U'
Declare @Start int,
@MaxCnt int,
@sql varchar(255)
Select @Start = 1
Select @MaxCnt = count(name) from #Tables
while @Start <= @MaxCnt
Begin
Declare @TableName varchar(75)
select @TableName = name from #Tables
where ID = @Start
print @TableName
Insert into #FinalResult
select object_name(a.id),a.name,b.name,a.length
from syscolumns a,systypes b
where a.type = b.type
and a.usertype = b.usertype
and a.id = object_id(@TableName)
select @Start = @Start + 1
End
select * from #FinalResult
order by TableName
----------------------
But again i am using while loop logic. is it possible to avoid WHILE LOOP in these kind of requirement or is it mandatory ?
Inputs are welcome !
karthik
March 26, 2008 at 6:03 am
What's wrong with just this?
select object_name(a.id),a.name,b.name,a.length
from syscolumns a,systypes b
where a.type = b.type
and a.usertype = b.usertype
order by object_name(a.id)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2008 at 6:25 am
You are correct ! Thanks !
karthik
March 26, 2008 at 7:53 pm
This works also and has the advantage of being portable to many of database environments:
[font="Courier New"]Select Table_Schema, Table_Name , Column_Name, DATA_TYPE
, Coalesce(Character_Maximum_Length, Numeric_Precision) as [Size]
From INFORMATION_SCHEMA.COLUMNS C
Where Exists (Select * From INFORMATION_SCHEMA.TABLES T
Where T.Table_Schema = C.Table_Schema
And T.Table_Name = C.Table_Name
And T.Table_Type = 'BASE TABLE')
[/font]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply