Informational Queries

Hello,
Are there any commands that would give infomation on all DBs on a SQL
server? (IE size, location of files, name, that sort of thing?) I have just
been given about twenty SQLservers and due to a person leaving I am hoping to
find ot some information about what is on them
I apprecitate the help.
jj
declare @.dbname sysname
if object_id('tempdb..#dbs')is not null drop table #dbs
select
[name]
into
#dbs
from
master..sysdatabases
while (select count(*) from #dbs)>0 begin
select top 1 @.dbname=[name] from #dbs
exec ('sp_helpdb [' + @.dbname + ']')
delete #dbs where [name]=@.dbname
end
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"John Jarrett" wrote:

> Hello,
> Are there any commands that would give infomation on all DBs on a SQL
> server? (IE size, location of files, name, that sort of thing?) I have just
> been given about twenty SQLservers and due to a person leaving I am hoping to
> find ot some information about what is on them
> I apprecitate the help.
> --
> jj
|||The system tables will provide you with the appropiate information, but....
the best thing is to query them via the predefined stored procedures, read
about the sp_help% procedures in BOL, thatll help you.
For example sp_helpdb, sp_helpfiles
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"John Jarrett" <JohnJarrett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:11569D6F-1381-4E14-9F1E-C0EFB2ABA943@.microsoft.com...
> Hello,
> Are there any commands that would give infomation on all DBs on a SQL
> server? (IE size, location of files, name, that sort of thing?) I have
> just
> been given about twenty SQLservers and due to a person leaving I am hoping
> to
> find ot some information about what is on them
> I apprecitate the help.
> --
> jj
|||See sps sp_helpdb and sp_helpfile in BOL.
AMB
"John Jarrett" wrote:

> Hello,
> Are there any commands that would give infomation on all DBs on a SQL
> server? (IE size, location of files, name, that sort of thing?) I have just
> been given about twenty SQLservers and due to a person leaving I am hoping to
> find ot some information about what is on them
> I apprecitate the help.
> --
> jj
sql

Indexes On Filegroups

Hi All,
I'm splitting a large database into filegroups. Files that join each other
are put in different filegroups to eliminate contentions.
Is it advisable to create Clustered indexes on different filegroup and not o
n
the base table filegroup.? I know it can be done on nonclustered indexes.
Thanks.
Message posted via http://www.webservertalk.comIf I understand you correctly, it cant be done. Clustered indexes need to be
on the sam filegroup as the table they are on. On SQL Server 2005 things are
slightly different, partitions need to be on the same FG.
MC

>"Naana via webservertalk.com" <u14055@.uwe> wrote in message
>news:5fa683e03f6c5@.uwe...
> Hi All,
> I'm splitting a large database into filegroups. Files that join each other
> are put in different filegroups to eliminate contentions.
> Is it advisable to create Clustered indexes on different filegroup and not
> on
> the base table filegroup.? I know it can be done on nonclustered indexes.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||Clustered indexes are not seperately stored. Its the order in which the
actual table is stored physically (for all practical purposes). So you can't
seperate the cluster index from the table in this version or SQL Server and
any newer versions. Hope this helps.
--
"Naana via webservertalk.com" wrote:

> Hi All,
> I'm splitting a large database into filegroups. Files that join each other
> are put in different filegroups to eliminate contentions.
> Is it advisable to create Clustered indexes on different filegroup and not
on
> the base table filegroup.? I know it can be done on nonclustered indexes.
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||Yes, it does help.
Thanks.
Omnibuzz wrote:
>Clustered indexes are not seperately stored. Its the order in which the
>actual table is stored physically (for all practical purposes). So you can'
t
>seperate the cluster index from the table in this version or SQL Server and
>any newer versions. Hope this helps.
>[quoted text clipped - 5 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1

Indexes and Filegroups

I'm fishing for some advice here.

I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.

I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)

I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?

I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.

So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?

The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.

many thanks

SteveI've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

if data set weighs in at around 6Gb assignation each table into its own filegroup not requeried

The Access tables don't have primary keys. MS SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

add new field int type with identity and set it as primary key

if you wont make quick application - do not use access 97 oledb connection to Mssql server. rebuld it to accessXP ADP project