Is there a limitation of how many db you can have in SQL Standard Edition?
Will it cause problem if we have too much? somewhere around 45 DBs.
Thanks.The documented limit is 32767 databases per server instance, so you have
some way to go yet! With the appropriate hardware 45 databases is certainly
no problem.
--
David Portas
SQL Server MVP
--|||Thanks for replied my post. My server is a Dell 8450 Quad-processors with 2
GB of RAM.
"David Portas" wrote:
> The documented limit is 32767 databases per server instance, so you have
> some way to go yet! With the appropriate hardware 45 databases is certainly
> no problem.
> --
> David Portas
> SQL Server MVP
> --
>
>|||David,
I think that when David Portas mentioned "appropriate hardware", he didn't
mean that there's some magic formula for 45 databases. It depends on what
those databases are doing -- how much data they contain, how many users
concurrently use each of them, what those users are doing, etc. If each
database has 100 concurrent users doing million-row queries, you'll quickly
run into a problem. On the other hand, if each database has 1 table with 10
rows of data and has a query run against it once a week, you'll be able to
support thousands of databases :)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David" <David@.discussions.microsoft.com> wrote in message
news:668C567C-E459-4ABB-A1F7-129BBADD6EB8@.microsoft.com...
> Thanks for replied my post. My server is a Dell 8450 Quad-processors with
2
> GB of RAM.
> "David Portas" wrote:
> > The documented limit is 32767 databases per server instance, so you have
> > some way to go yet! With the appropriate hardware 45 databases is
certainly
> > no problem.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
> >|||David,
In addition to David's and Adam's excellent responses, you may find
limitations with SQL-DMO which will manifest through Enterprise Manager.
After a certain number of databases, you will find that enumeration of
databases will take a long time when you expand the databases node in
the tree. At some point you will find that it takes longer than you are
willing to wait for it, and you will have to administer your databases
using T-SQL.
SMO in SQL Server 2005 addresses this issue by using partial
instantiation (aka delayed instantiation) and caching.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
David wrote:
> Is there a limitation of how many db you can have in SQL Standard Edition?
> Will it cause problem if we have too much? somewhere around 45 DBs.
> Thanks.
>
Showing posts with label dbs. Show all posts
Showing posts with label dbs. Show all posts
Wednesday, March 28, 2012
Tuesday, March 20, 2012
Number of columns for all tables in a database
Hi - I apologize in advance for the newbie question...
I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.
I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)
Is there a query that can output a grid listing the number of fields
per each table in a selected database?
If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.
I REALLY appreciate any help sent my way.
Cheers!
Joel
Hi Joel
No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.
There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.
exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'
If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)
Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)
Hopefully, this has given you a start...
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegr oups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>
|||Just do a GROUP BY and you are there:
SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
For number of rows, we need to know what version of SQL Server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegr oups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>
|||Thank you BOTH so much for your help.
I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.
We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...
Thanks!
Tibor Karaszi wrote:[vbcol=seagreen]
> Just do a GROUP BY and you are there:
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
> For number of rows, we need to know what version of SQL Server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
> news:1159539224.878544.293450@.i42g2000cwa.googlegr oups.com...
|||Does the row count need to be exact? If not, use the sysindexes system table, something like:
SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt
And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname') WITH COUNT_ROWS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159544274.480475.86270@.b28g2000cwb.googlegro ups.com...
> Thank you BOTH so much for your help.
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
> Thanks!
>
> Tibor Karaszi wrote:
>
|||Joel,
I think I am a little more bored than the rest of them.
--This procedure will loop through the database list and load a table
--in tempdb with the column count for each table.
USE tempdb
IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END
CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_namevarchar(200) NOT NULL
, col_cntint NULL )
DECLARE @.db_namevarchar(200)
DECLARE @.count as int
DECLARE @.cmdvarchar(2000)
SET @.cmd = ''
SET @.db_name = ''
DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null
OPEN dbs
FETCH NEXT FROM dbs into @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.db_name
SET@.cmd = 'USE [' + @.db_name + ']'
PRINT@.cmd
EXECUTE( @.cmd)
SET @.cmd = 'INSERT INTO tempdb..table_specs
SELECT''' + @.db_name + ''', a.name, count(*)
FROM[' + @.db_name + ']..sysobjects a, [' + @.db_name + ']..syscolumns
b
WHEREa.id= b.id
ANDa.type= ''U''
GROUP BY a.name'
EXECUTE (@.cmd)
PRINT @.cmd
FETCH NEXT FROM dbs into @.db_name
END
CLOSE dbs
DEALLOCATE dbs
-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs
I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.
I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)
Is there a query that can output a grid listing the number of fields
per each table in a selected database?
If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.
I REALLY appreciate any help sent my way.
Cheers!
Joel
Hi Joel
No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.
There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.
exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'
If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)
Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)
Hopefully, this has given you a start...
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegr oups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>
|||Just do a GROUP BY and you are there:
SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
For number of rows, we need to know what version of SQL Server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegr oups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>
|||Thank you BOTH so much for your help.
I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.
We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...
Thanks!
Tibor Karaszi wrote:[vbcol=seagreen]
> Just do a GROUP BY and you are there:
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
> For number of rows, we need to know what version of SQL Server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
> news:1159539224.878544.293450@.i42g2000cwa.googlegr oups.com...
|||Does the row count need to be exact? If not, use the sysindexes system table, something like:
SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt
And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname') WITH COUNT_ROWS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159544274.480475.86270@.b28g2000cwb.googlegro ups.com...
> Thank you BOTH so much for your help.
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
> Thanks!
>
> Tibor Karaszi wrote:
>
|||Joel,
I think I am a little more bored than the rest of them.
--This procedure will loop through the database list and load a table
--in tempdb with the column count for each table.
USE tempdb
IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END
CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_namevarchar(200) NOT NULL
, col_cntint NULL )
DECLARE @.db_namevarchar(200)
DECLARE @.count as int
DECLARE @.cmdvarchar(2000)
SET @.cmd = ''
SET @.db_name = ''
DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null
OPEN dbs
FETCH NEXT FROM dbs into @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.db_name
SET@.cmd = 'USE [' + @.db_name + ']'
PRINT@.cmd
EXECUTE( @.cmd)
SET @.cmd = 'INSERT INTO tempdb..table_specs
SELECT''' + @.db_name + ''', a.name, count(*)
FROM[' + @.db_name + ']..sysobjects a, [' + @.db_name + ']..syscolumns
b
WHEREa.id= b.id
ANDa.type= ''U''
GROUP BY a.name'
EXECUTE (@.cmd)
PRINT @.cmd
FETCH NEXT FROM dbs into @.db_name
END
CLOSE dbs
DEALLOCATE dbs
-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs
Number of columns for all tables in a database
Hi - I apologize in advance for the newbie question...
I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.
I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)
Is there a query that can output a grid listing the number of fields
per each table in a selected database?
If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.
I REALLY appreciate any help sent my way.
Cheers!
JoelHi Joel
No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.
There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.
exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'
If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)
Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)
Hopefully, this has given you a start...
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Just do a GROUP BY and you are there:
SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
For number of rows, we need to know what version of SQL Server...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Thank you BOTH so much for your help.
I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.
We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...
Thanks!
Tibor Karaszi wrote:
> Just do a GROUP BY and you are there:
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
> For number of rows, we need to know what version of SQL Server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
> news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> > Hi - I apologize in advance for the newbie question...
> >
> > I have been asked to gather some db statistics. We have 11 dbs and
> > some have 70 or more tables.
> >
> > I need to gather is number of fields for each table in the database.
> > I've used this so far:
> > SELECT COUNT(*)
> > FROM INFORMATION_SCHEMA.COLUMNS
> > WHERE TABLE_NAME = 'pub_info'
> > and doing this for each table in each database. However, this is going
> > to take me three lifetimes. ;o)
> >
> > Is there a query that can output a grid listing the number of fields
> > per each table in a selected database?
> >
> > If the query could simultaneously list the number of rows as well that
> > would be a bonus, but not necessary.
> >
> > I REALLY appreciate any help sent my way.
> >
> > Cheers!
> > Joel
> >|||Does the row count need to be exact? If not, use the sysindexes system table, something like:
SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt
And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname') WITH COUNT_ROWS
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159544274.480475.86270@.b28g2000cwb.googlegroups.com...
> Thank you BOTH so much for your help.
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
> Thanks!
>
> Tibor Karaszi wrote:
>> Just do a GROUP BY and you are there:
>> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
>> FROM INFORMATION_SCHEMA.COLUMNS
>> GROUP BY TABLE_NAME
>> For number of rows, we need to know what version of SQL Server...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
>> news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
>> > Hi - I apologize in advance for the newbie question...
>> >
>> > I have been asked to gather some db statistics. We have 11 dbs and
>> > some have 70 or more tables.
>> >
>> > I need to gather is number of fields for each table in the database.
>> > I've used this so far:
>> > SELECT COUNT(*)
>> > FROM INFORMATION_SCHEMA.COLUMNS
>> > WHERE TABLE_NAME = 'pub_info'
>> > and doing this for each table in each database. However, this is going
>> > to take me three lifetimes. ;o)
>> >
>> > Is there a query that can output a grid listing the number of fields
>> > per each table in a selected database?
>> >
>> > If the query could simultaneously list the number of rows as well that
>> > would be a bonus, but not necessary.
>> >
>> > I REALLY appreciate any help sent my way.
>> >
>> > Cheers!
>> > Joel
>> >
>|||Joel,
I think I am a little more bored than the rest of them.
--This procedure will loop through the database list and load a table
-- in tempdb with the column count for each table.
USE tempdb
IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END
CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_name varchar(200) NOT NULL
, col_cnt int NULL )
DECLARE @.db_name varchar(200)
DECLARE @.count as int
DECLARE @.cmd varchar(2000)
SET @.cmd = ''
SET @.db_name = ''
DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null
OPEN dbs
FETCH NEXT FROM dbs into @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.db_name
SET @.cmd = 'USE [' + @.db_name + ']'
PRINT @.cmd
EXECUTE ( @.cmd)
SET @.cmd = 'INSERT INTO tempdb..table_specs
SELECT ''' + @.db_name + ''', a.name, count(*)
FROM [' + @.db_name + ']..sysobjects a, [' + @.db_name + ']..syscolumns
b
WHERE a.id = b.id
AND a.type = ''U''
GROUP BY a.name'
EXECUTE (@.cmd)
PRINT @.cmd
FETCH NEXT FROM dbs into @.db_name
END
CLOSE dbs
DEALLOCATE dbs
-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs
I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.
I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)
Is there a query that can output a grid listing the number of fields
per each table in a selected database?
If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.
I REALLY appreciate any help sent my way.
Cheers!
JoelHi Joel
No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.
There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.
exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'
If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)
Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)
Hopefully, this has given you a start...
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Just do a GROUP BY and you are there:
SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
For number of rows, we need to know what version of SQL Server...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Thank you BOTH so much for your help.
I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.
We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...
Thanks!
Tibor Karaszi wrote:
> Just do a GROUP BY and you are there:
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
> For number of rows, we need to know what version of SQL Server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
> news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> > Hi - I apologize in advance for the newbie question...
> >
> > I have been asked to gather some db statistics. We have 11 dbs and
> > some have 70 or more tables.
> >
> > I need to gather is number of fields for each table in the database.
> > I've used this so far:
> > SELECT COUNT(*)
> > FROM INFORMATION_SCHEMA.COLUMNS
> > WHERE TABLE_NAME = 'pub_info'
> > and doing this for each table in each database. However, this is going
> > to take me three lifetimes. ;o)
> >
> > Is there a query that can output a grid listing the number of fields
> > per each table in a selected database?
> >
> > If the query could simultaneously list the number of rows as well that
> > would be a bonus, but not necessary.
> >
> > I REALLY appreciate any help sent my way.
> >
> > Cheers!
> > Joel
> >|||Does the row count need to be exact? If not, use the sysindexes system table, something like:
SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt
And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname') WITH COUNT_ROWS
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159544274.480475.86270@.b28g2000cwb.googlegroups.com...
> Thank you BOTH so much for your help.
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
> Thanks!
>
> Tibor Karaszi wrote:
>> Just do a GROUP BY and you are there:
>> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
>> FROM INFORMATION_SCHEMA.COLUMNS
>> GROUP BY TABLE_NAME
>> For number of rows, we need to know what version of SQL Server...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
>> news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
>> > Hi - I apologize in advance for the newbie question...
>> >
>> > I have been asked to gather some db statistics. We have 11 dbs and
>> > some have 70 or more tables.
>> >
>> > I need to gather is number of fields for each table in the database.
>> > I've used this so far:
>> > SELECT COUNT(*)
>> > FROM INFORMATION_SCHEMA.COLUMNS
>> > WHERE TABLE_NAME = 'pub_info'
>> > and doing this for each table in each database. However, this is going
>> > to take me three lifetimes. ;o)
>> >
>> > Is there a query that can output a grid listing the number of fields
>> > per each table in a selected database?
>> >
>> > If the query could simultaneously list the number of rows as well that
>> > would be a bonus, but not necessary.
>> >
>> > I REALLY appreciate any help sent my way.
>> >
>> > Cheers!
>> > Joel
>> >
>|||Joel,
I think I am a little more bored than the rest of them.
--This procedure will loop through the database list and load a table
-- in tempdb with the column count for each table.
USE tempdb
IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END
CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_name varchar(200) NOT NULL
, col_cnt int NULL )
DECLARE @.db_name varchar(200)
DECLARE @.count as int
DECLARE @.cmd varchar(2000)
SET @.cmd = ''
SET @.db_name = ''
DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null
OPEN dbs
FETCH NEXT FROM dbs into @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.db_name
SET @.cmd = 'USE [' + @.db_name + ']'
PRINT @.cmd
EXECUTE ( @.cmd)
SET @.cmd = 'INSERT INTO tempdb..table_specs
SELECT ''' + @.db_name + ''', a.name, count(*)
FROM [' + @.db_name + ']..sysobjects a, [' + @.db_name + ']..syscolumns
b
WHERE a.id = b.id
AND a.type = ''U''
GROUP BY a.name'
EXECUTE (@.cmd)
PRINT @.cmd
FETCH NEXT FROM dbs into @.db_name
END
CLOSE dbs
DEALLOCATE dbs
-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs
Number of columns for all tables in a database
Hi - I apologize in advance for the newbie question...
I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.
I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)
Is there a query that can output a grid listing the number of fields
per each table in a selected database?
If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.
I REALLY appreciate any help sent my way.
Cheers!
JoelHi Joel
No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.
There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.
exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'
If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)
Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)
Hopefully, this has given you a start...
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Just do a GROUP BY and you are there:
SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
For number of rows, we need to know what version of SQL Server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Thank you BOTH so much for your help.
I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.
We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...
Thanks!
Tibor Karaszi wrote:[vbcol=seagreen]
> Just do a GROUP BY and you are there:
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
> For number of rows, we need to know what version of SQL Server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
> news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...|||Does the row count need to be exact? If not, use the sysindexes system table
, something like:
SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt
And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname') WITH COUNT_ROWS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159544274.480475.86270@.b28g2000cwb.googlegroups.com...
> Thank you BOTH so much for your help.
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
> Thanks!
>
> Tibor Karaszi wrote:
>|||Joel,
I think I am a little more bored than the rest of them.
--This procedure will loop through the database list and load a table
-- in tempdb with the column count for each table.
USE tempdb
IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END
CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_name varchar(200) NOT NULL
, col_cnt int NULL )
DECLARE @.db_name varchar(200)
DECLARE @.count as int
DECLARE @.cmd varchar(2000)
SET @.cmd = ''
SET @.db_name = ''
DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null
OPEN dbs
FETCH NEXT FROM dbs into @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.db_name
SET @.cmd = 'USE [' + @.db_name + ']'
PRINT @.cmd
EXECUTE ( @.cmd)
SET @.cmd = 'INSERT INTO tempdb..table_specs
SELECT ''' + @.db_name + ''', a.name, count(*)
FROM [' + @.db_name + ']..sysobjects a, [' + @.db_name + ']..syscolumn
s
b
WHERE a.id = b.id
AND a.type = ''U''
GROUP BY a.name'
EXECUTE (@.cmd)
PRINT @.cmd
FETCH NEXT FROM dbs into @.db_name
END
CLOSE dbs
DEALLOCATE dbs
-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs
I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.
I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)
Is there a query that can output a grid listing the number of fields
per each table in a selected database?
If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.
I REALLY appreciate any help sent my way.
Cheers!
JoelHi Joel
No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.
There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.
exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'
If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)
Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)
Hopefully, this has given you a start...
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Just do a GROUP BY and you are there:
SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
For number of rows, we need to know what version of SQL Server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Thank you BOTH so much for your help.
I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.
We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...
Thanks!
Tibor Karaszi wrote:[vbcol=seagreen]
> Just do a GROUP BY and you are there:
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
> For number of rows, we need to know what version of SQL Server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
> news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...|||Does the row count need to be exact? If not, use the sysindexes system table
, something like:
SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt
And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname') WITH COUNT_ROWS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159544274.480475.86270@.b28g2000cwb.googlegroups.com...
> Thank you BOTH so much for your help.
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
> Thanks!
>
> Tibor Karaszi wrote:
>|||Joel,
I think I am a little more bored than the rest of them.
--This procedure will loop through the database list and load a table
-- in tempdb with the column count for each table.
USE tempdb
IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END
CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_name varchar(200) NOT NULL
, col_cnt int NULL )
DECLARE @.db_name varchar(200)
DECLARE @.count as int
DECLARE @.cmd varchar(2000)
SET @.cmd = ''
SET @.db_name = ''
DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null
OPEN dbs
FETCH NEXT FROM dbs into @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.db_name
SET @.cmd = 'USE [' + @.db_name + ']'
PRINT @.cmd
EXECUTE ( @.cmd)
SET @.cmd = 'INSERT INTO tempdb..table_specs
SELECT ''' + @.db_name + ''', a.name, count(*)
FROM [' + @.db_name + ']..sysobjects a, [' + @.db_name + ']..syscolumn
s
b
WHERE a.id = b.id
AND a.type = ''U''
GROUP BY a.name'
EXECUTE (@.cmd)
PRINT @.cmd
FETCH NEXT FROM dbs into @.db_name
END
CLOSE dbs
DEALLOCATE dbs
-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs
Subscribe to:
Posts (Atom)