I have created a proc in sqlserver which counts the no of rec in each table
in the db, I want to create the same in sybase, as I am new to sybase
please help me in converting this sp in to sybase, as select count(*) will be time consuming for me as the no of tables are 1000 +
SQL Server proc. and query to get the results.
FUNCTION dbo.UDF_Tab_Row_count (
sysname -- Table to retrieve Row Count
RETURNS INT -- Row count of the table, NULL if not found.
DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID INT -- Object ID
SET @nObjectID = OBJECT_ID(@sTableName)
-- Object might not be found
IF @nObjectID is null RETURN NULL
SELECT TOP 1 @nRowCount = rows
WHERE id = @nObjectID AND indid < 2
----- end of function----
[name], dbo.UDF_Tab_Row_count ([name]) as [Row Count]
WHERE type='U' and name != 'dtproperties'
ORDER BY [name]
--out put will be table name and number of records in each table.----
name Row Count
That code will not work, it SUMs the indices for o.name (depending on the version, if table_a has 1 million rows, and 6 indices, you will get "table_a 6,000,000").
This is for Pre-15.0:
FROM sysobjects o,
WHERE o.id = i.id
AND o.sysstat2 & 1024 = 0 -- not remote
AND o.sysstat2 & 2048 = 0 -- not proxy
AND (i.indid = 0 OR i.indid = 1) -- Heap or ClustIdx only
ORDER BY o.name
WHERE type = "U"
AND sysstat2 & 1024 = 0 -- not remote
AND sysstat2 & 2048 = 0 -- not proxy
ORDER BY name
You do not need to code a function; the ROWCNT() or ROW_COUNT() function is already supplied by Sybase.