Script that Return the number of Rows for each Table on a DB

I Have 2 separate data bases with the same Tables and records, I create
a batch to synchronize my data every 2 hours but now I need a Script
that Return the number of Rows for each Table on each DB.
Can someone give me an Idea or the solution for this, I will really
appreciate it.This is what the code I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN ('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||This is what I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN
('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||Run this query against each database:
SELECT DISTINCT TOP 100 PERCENT dbo.sysobjects.name, dbo.sysindexes.rowcnt
FROM dbo.sysobjects INNER JOIN
dbo.sysindexes ON dbo.sysobjects.id = dbo.sysindexes.id
WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysindexes.status = 0)
ORDER BY dbo.sysobjects.name
"imagabo" wrote:

> I Have 2 separate data bases with the same Tables and records, I create
> a batch to synchronize my data every 2 hours but now I need a Script
> that Return the number of Rows for each Table on each DB.
> Can someone give me an Idea or the solution for this, I will really
> appreciate it.
>|||This is it, Thank you so much bschaettle, I own you one.

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.
Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer.SetDefaultInitFields(GetType(StoredProce dure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.
|||http://www.sqlteam.com/item.asp?ItemID=23185
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database with
> about a hundred tables and a few hundred derived objects - views, functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.|||http://www.sqlteam.com/item.asp?ItemID=23185
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database with
> about a hundred tables and a few hundred derived objects - views, functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer. SetDefaultInitFields(GetType(StoredProce
dure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.|||http://www.sqlteam.com/item.asp?ItemID=23185
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database wit
h
> about a hundred tables and a few hundred derived objects - views, function
s,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.