Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?
See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)

> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.

> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...

> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)

> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)

Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)

Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be abl
e
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)

> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.

> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...

> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)

> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)sql

Little bit confused about databases on CE

Hi there,

i need a database for my Windows CE application which i can update from a desktop application.

I tried the SqlCeConnection. This works good on the device, but i found out, that i need a sql server on the desktop or someone else to get access to the device server. This is a problem for me, because i cannot insall such a sever on the desktop.

So i searched and searched....I found infos about the ole connection, but i cant find the namespace?!?

Can anyone give me a hint what the best solution could be?

Im using Visual Studio 2005 and a CE device.

Thanks a lot

You can use VS 2005 'Add New Datasource Wizard' under Data menu OR in Server Explorer ==> Add Connection to update the database on device. Also, with introduction of SQL Server Compact/Mobile/Everywhere Edition, you can copy the database file to a desktop and you can use System.Data.SqlServerCe.dll that is installed on desktop (If not installed, download and install this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=898913&SiteID=1).

Thanks,

Laxmi

Litespeed question

Hi,
We are using litespeed(version 4.8) to backup databases on SQL Server
2005(SP2 database) but everytime few databases are left out although drive
has enough free space and also it doesnot generate any error
Script for the same is:
EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempdb'')
exec master.dbo.xp_backup_database
@.database = ''?'',
@.filename = ''Backupdrivepath\FULL_?.BKP'',
@.backupname = ''FULL ? backup'',
@.desc = ''FULL Backup of ?'', @.init = 1, @.logging = 0,
@.compressionlevel = 5, @.comment = '''', @.with = ''SKIP'',
@.with = ''STATS = 10'' '
Please help me.
ThanksHi Manu,
If you are backing up to Tape drive or network storage then see if there is
any issue with netowrk connectivity Also look at SQL server logs to determine
all databases have been selected for backup.
"manu" wrote:
> Hi,
> We are using litespeed(version 4.8) to backup databases on SQL Server
> 2005(SP2 database) but everytime few databases are left out although drive
> has enough free space and also it doesnot generate any error
> Script for the same is:
> EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempdb'')
> exec master.dbo.xp_backup_database
> @.database = ''?'',
> @.filename = ''Backupdrivepath\FULL_?.BKP'',
> @.backupname = ''FULL ? backup'',
> @.desc = ''FULL Backup of ?'', @.init = 1, @.logging = 0,
> @.compressionlevel = 5, @.comment = '''', @.with = ''SKIP'',
> @.with = ''STATS = 10'' '
>
> Please help me.
> Thanks
>

Listing Size of all user Databases in SQL2005

Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?

Thanks,

Refer to Books Online, Topic: sys.database_files

This will return the size as the number of 8K pages, so do the math in your query.

|||sp_helpdb will generate info about the dbname and size|||

select * from sys.master_files

Will also give you useful information.

Listing Size of all user Databases in SQL2005

Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?

Thanks,

Refer to Books Online, Topic: sys.database_files

This will return the size as the number of 8K pages, so do the math in your query.

|||sp_helpdb will generate info about the dbname and size|||

select * from sys.master_files

Will also give you useful information.

listing non system databases

I know sp_databases can list all the databases, but how can i only list
non-system databases? I just want to get a list of user databasesYou can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
system table to the the list of all databases. You can avoid the master,
model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
clause to get the list of non-system databases.
Anith|||Hi
If you profile SQL Management Studio in SQL 2005 to see how it determines a
system database, you will see that it queries sysdatabases and determines
that the are system database by their name.
John
"Brian Henry" <nospam@.nospam.com> wrote in message
news:u5cw%23c7CGHA.3064@.TK2MSFTNGP10.phx.gbl...
>I know sp_databases can list all the databases, but how can i only list
>non-system databases? I just want to get a list of user databases
>|||> You can query the INFORMATION_SCHEMA.SCHEMATA view
Note that this view has a different meaning in 2005, where it returns the sc
hemas in the current
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u734707CGHA.2704@.TK2MSFTNGP15.phx.gbl...
> You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases sys
tem table to the the
> list of all databases. You can avoid the master, model, msdb, mssqlweb, te
mpdb, Pubs & Northwind
> databases in the WHERE clause to get the list of non-system databases.
> --
> Anith
>|||Yes, thanks. In 2005, use sys.databases instead.
Anith

List of Users and Databases

Hi there,
I am wanting to get a list of databases (in sql 2000) and the users
who have access to each database. Does anyone know what SQL statement
I can use to get this information? Thanks
RichardTry this:
Declare @.Databasename varchar(100)
Declare @.MySQLString nvarchar(100)
SET NOCOUNT ON
declare c_Databasename insensitive cursor for
select name from master..sysdatabases
open c_Databasename
fetch next from c_Databasename into @.Databasename
while @.@.fetch_status = 0
BEGIN
SET @.MySQLString = 'Select '''+@.Databasename+''' [Database],name [users]
from '+@.Databasename+'.dbo.sysusers where sid is not null'
exec sp_executesql @.MySQLString
fetch next from c_Databasename into @.Databasename
END
close c_Databasename
deallocate c_Databasename
"Richie.Cunningham" wrote:
> Hi there,
> I am wanting to get a list of databases (in sql 2000) and the users
> who have access to each database. Does anyone know what SQL statement
> I can use to get this information? Thanks
> Richard
>|||Richie
Try something like that
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
"Richie.Cunningham" <richplimmer@.yahoo.co.nz> wrote in message
news:1184017601.784136.45900@.z28g2000prd.googlegroups.com...
> Hi there,
> I am wanting to get a list of databases (in sql 2000) and the users
> who have access to each database. Does anyone know what SQL statement
> I can use to get this information? Thanks
> Richard
>

List of Users and Databases

Hi there,
I am wanting to get a list of databases (in sql 2000) and the users
who have access to each database. Does anyone know what SQL statement
I can use to get this information? Thanks
Richard
Try this:
Declare @.Databasename varchar(100)
Declare @.MySQLString nvarchar(100)
SET NOCOUNT ON
declare c_Databasename insensitive cursor for
select name from master..sysdatabases
open c_Databasename
fetch next from c_Databasename into @.Databasename
while @.@.fetch_status = 0
BEGIN
SET @.MySQLString = 'Select '''+@.Databasename+''' [Database],name [users]
from '+@.Databasename+'.dbo.sysusers where sid is not null'
exec sp_executesql @.MySQLString
fetch next from c_Databasename into @.Databasename
END
close c_Databasename
deallocate c_Databasename
"Richie.Cunningham" wrote:

> Hi there,
> I am wanting to get a list of databases (in sql 2000) and the users
> who have access to each database. Does anyone know what SQL statement
> I can use to get this information? Thanks
> Richard
>
|||Richie
Try something like that
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
"Richie.Cunningham" <richplimmer@.yahoo.co.nz> wrote in message
news:1184017601.784136.45900@.z28g2000prd.googlegro ups.com...
> Hi there,
> I am wanting to get a list of databases (in sql 2000) and the users
> who have access to each database. Does anyone know what SQL statement
> I can use to get this information? Thanks
> Richard
>

List of Users and Databases

Hi there,
I am wanting to get a list of databases (in sql 2000) and the users
who have access to each database. Does anyone know what SQL statement
I can use to get this information? Thanks
RichardTry this:
Declare @.Databasename varchar(100)
Declare @.MySQLString nvarchar(100)
SET NOCOUNT ON
declare c_Databasename insensitive cursor for
select name from master..sysdatabases
open c_Databasename
fetch next from c_Databasename into @.Databasename
while @.@.fetch_status = 0
BEGIN
SET @.MySQLString = 'Select '''+@.Databasename+''' [Database],name [us
ers]
from '+@.Databasename+'.dbo.sysusers where sid is not null'
exec sp_executesql @.MySQLString
fetch next from c_Databasename into @.Databasename
END
close c_Databasename
deallocate c_Databasename
"Richie.Cunningham" wrote:

> Hi there,
> I am wanting to get a list of databases (in sql 2000) and the users
> who have access to each database. Does anyone know what SQL statement
> I can use to get this information? Thanks
> Richard
>|||Richie
Try something like that
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
"Richie.Cunningham" <richplimmer@.yahoo.co.nz> wrote in message
news:1184017601.784136.45900@.z28g2000prd.googlegroups.com...
> Hi there,
> I am wanting to get a list of databases (in sql 2000) and the users
> who have access to each database. Does anyone know what SQL statement
> I can use to get this information? Thanks
> Richard
>

list of databases in Analysis Services

Hi all,

I have to write a program to read the names of databases in Analysis
Services. I don't know which table I can get this information from.

Thanks a bunchHippi wrote:
> Hi all,
> I have to write a program to read the names of databases in Analysis
> Services. I don't know which table I can get this information from.
> Thanks a bunch

would this work:

select dbid, name
from master..sysdatabases
where has_dbaccess(name) = 1

--
David Rowland
http://dbmonitor.tripod.com|||No, it doesn't work. It shows all the databases I have in SQL Server

list of Databases

hello anybody now

i need to find how many Databases in my local SQL server (programatically).i know i have 8 databases is there but i need to print each and every database.

can anybody help me.

thanx

kiran.

try the command...

sp_databases

look into master > stored procedures > sp_databases
and customize it|||

Thanxs

its working rom-rom

|||kiran, glad i've been of help, thanks|||

The problem with sp_databases is that it displays the system databases, together with Northwind and Pubs. I use this to identify my databases:

USE master
GO
SELECT name FROM sysdatabases WHERE dbid>6

|||select * from information_schema.schemata is a polically correct version of the previous post

List of Databases

I am writing an Active Server Page application that will get all of the
databases that a user can access. Is there a way I can do this with Active
Server Pages? I also will provide the users with the option to change
records, table structure, etc with this application.
I will also be developing an asp.net (C#) project that will do the same
thing.
If anyone can provide code snippets or anything that would be great.
Thanks,
Rick Langschultz
Web Developer/IT Manager
Rick Langschultz wrote:
> I am writing an Active Server Page application that will get all of
> the databases that a user can access. Is there a way I can do this
> with Active Server Pages? I also will provide the users with the
> option to change records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the
> same thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
What database? Should we assume that it's SQL Server given the inclusion of
the sqlserver groups in your crosspost?
Please don't make us guess. We need to know the type and version of rdbms
you are using.
You left out the dotnet groups that could have helped you with the .Net
code you need (I'm responding from the .asp group which is for classic ASP
questions)
For starters, do a search at www.aspfaq.com. Here's one of the articles you
will find:
http://www.aspfaq.com/show.asp?id=2456
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
|||the easiest way is:
select name from master.dbo.sysdatabases
Leo
"Rick Langschultz" wrote:

> I am writing an Active Server Page application that will get all of the
> databases that a user can access. Is there a way I can do this with Active
> Server Pages? I also will provide the users with the option to change
> records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the same
> thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
>
>

List of Databases

I am writing an Active Server Page application that will get all of the
databases that a user can access. Is there a way I can do this with Active
Server Pages? I also will provide the users with the option to change
records, table structure, etc with this application.
I will also be developing an asp.net (C#) project that will do the same
thing.
If anyone can provide code snippets or anything that would be great.
Thanks,
Rick Langschultz
Web Developer/IT Manager
Rick Langschultz wrote:
> I am writing an Active Server Page application that will get all of
> the databases that a user can access. Is there a way I can do this
> with Active Server Pages? I also will provide the users with the
> option to change records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the
> same thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
What database? Should we assume that it's SQL Server given the inclusion of
the sqlserver groups in your crosspost?
Please don't make us guess. We need to know the type and version of rdbms
you are using.
You left out the dotnet groups that could have helped you with the .Net
code you need (I'm responding from the .asp group which is for classic ASP
questions)
For starters, do a search at www.aspfaq.com. Here's one of the articles you
will find:
http://www.aspfaq.com/show.asp?id=2456
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
|||the easiest way is:
select name from master.dbo.sysdatabases
Leo
"Rick Langschultz" wrote:

> I am writing an Active Server Page application that will get all of the
> databases that a user can access. Is there a way I can do this with Active
> Server Pages? I also will provide the users with the option to change
> records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the same
> thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
>
>

List of Databases

I am writing an Active Server Page application that will get all of the
databases that a user can access. Is there a way I can do this with Active
Server Pages? I also will provide the users with the option to change
records, table structure, etc with this application.
I will also be developing an asp.net (C#) project that will do the same
thing.
If anyone can provide code snippets or anything that would be great.
Thanks,
Rick Langschultz
Web Developer/IT ManagerRick Langschultz wrote:
> I am writing an Active Server Page application that will get all of
> the databases that a user can access. Is there a way I can do this
> with Active Server Pages? I also will provide the users with the
> option to change records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the
> same thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
What database? Should we assume that it's SQL Server given the inclusion of
the sqlserver groups in your crosspost?
Please don't make us guess. We need to know the type and version of rdbms
you are using.
You left out the dotnet groups that could have helped you with the .Net
code you need (I'm responding from the .asp group which is for classic ASP
questions)
For starters, do a search at www.aspfaq.com. Here's one of the articles you
will find:
http://www.aspfaq.com/show.asp?id=2456
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||the easiest way is:
select name from master.dbo.sysdatabases
Leo
"Rick Langschultz" wrote:

> I am writing an Active Server Page application that will get all of the
> databases that a user can access. Is there a way I can do this with Active
> Server Pages? I also will provide the users with the option to change
> records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the same
> thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
>
>

List of columns from tables across databases.

Hey guys,

Couldn't find this anywhere in google.

I want a list of all database column names for a specific table/view
from across database.

I tried this...
----------------
Select *
>From Information_Schema.Columns
----------------

I also tried this...

----------------
select syscolumns.name, sysobjects.name, * from syscolumns, sysobjects
where
sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
----------------

These queries return information about the CURRENT database.

But, if I want to do it ACROSS database or across servers.. how can I
do this?

I will express my gratitude to everyone who is kind enough to answer
this question. (I've been stuck with this problem for a while now.)

Thanks!

OhMyGaw!Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_schema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_schema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--

List of all relations of databases table.

Hi,

How to get list of all relations of certein database's table?

Use Visual Studio for Databases and create a diagram|||

lupina:

Hi,

How to get list of all relations of certein database's table?

You can also create the diagram in SQL Server Management Studio admin permissions required, try the link below for a step by step guide. Hope this helps.

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx

|||

Thanks for quick response, but I need to get this information through SQL query or SQLConnection object.

eg: that query return all tables with all constraints of current DB (unique keys and so on),
SELECT*FROMINFORMATION_SCHEMA.KEY_COLUMN_USAGEAS COLS
INNERJOININFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON COLS.CONSTRAINT_NAME= CONS.CONSTRAINT_NAME

WHERE COLS.CONSTRAINT_CATALOG=DB_NAME()
ORDERBY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

but I don't know how get list of relations ( names of Table1.column_foreignKey1 related to Table2.Column_foreignKey2).

|||

To generate the relationship with the diagraming tool is simple but to get that info in code it gets very complicated, I have found two resources to take you in the right direction. I think registering is required in the second link because it is a forum post. Hope this helps.

http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=164&messageid=117824

|||

I found the solution on one of the newsgroups:

create procedure usp_findreferences

@.tbname sysname=null

as

set nocount on

Print 'Referenced:'

select c1.table_name,

c1.column_name,

fkey=r.constraint_name,

referenced_parent_table=c2.table_name,

c2.column_name

from information_schema.constraint_column_usage c1 join

information_schema.referential_constraints r on

c1.constraint_name=r.constraint_name

join information_schema.constraint_column_usage c2 on

r.unique_constraint_name=c2.constraint_name

where c1.table_name=coalesce(@.tbname,c1.table_name)

order by case when @.tbname is null then c1.table_name else c2.table_name end

print ''

print 'Referencing:'

select c1.table_name,

c1.column_name,

fkey = r.constraint_name,

referencing_child_table = c2.table_name,

c2.column_name

from information_schema.constraint_column_usage c1 join

information_schema.referential_constraints r on

c1.constraint_name=r.unique_constraint_name

join information_schema.constraint_column_usage c2 on

r.constraint_name=c2.constraint_name

where c1.table_name=coalesce(@.tbname,c1.table_name)

order by case when @.tbname is null then c1.table_name else c2.table_name end

go

|||I glad to see you have found a solution, now you know it is not simple.

List of all databases with their data & log files?

Is there an easy way to print out a simple text report of
all database names plus their internal database filenames
& logfile names plus the actual physical locations of each
file in SQL Server 2000?
I'm an Oracle admin who's just inherited an SQL Server
full of multiple databases and I need to make a structural
diagram of what all lives where inside this server. In
Oracle, a simple sql script dumps out a text list of all
these kinds of things, but all I've been able to discover
thus far in MS SQL Enterprise Manager is an unfriendly GUI
interface that makes you have to repeatedly point, click,
and browse many times over and over again and again to get
this info one tiny piece at a time, which isn't very
efficient.New to MS SQL wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
Try this:
exec sp_MSforeachDB "sp_helpdb ?"
sp_helpdb by itself will give you basic database information for all
databases
sp_helpfile will give you the files used in the currently selected
database
Passing a database name to sp_helpdb gives both results and the
sp_MSforeachDB undocumented stored procedure automatically iterates
through the list of databases on the server and generates multiple
results sets.
David G.|||Before you start getting bent out of shape over SQL Server, there an easy way
to accompish this. :-)
Open up Query Analyzer, select the Master database and type in the following
query:
select name, filename from sysdatabases
This will give you a quick list of all the database on the SQL Server
machine adn their physical location. If you want more info, let me know and
we can go from there.
Scott
"New to MS SQL" wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
>|||> select name, filename from sysdatabases
... and if on 2000, you can join sysaltfiles to get sizing information...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLScott" <SQLScott@.discussions.microsoft.com> wrote in message
news:CC55215A-B041-4370-8D87-8C4ABDBB0072@.microsoft.com...
> Before you start getting bent out of shape over SQL Server, there an easy way
> to accompish this. :-)
> Open up Query Analyzer, select the Master database and type in the following
> query:
> select name, filename from sysdatabases
> This will give you a quick list of all the database on the SQL Server
> machine adn their physical location. If you want more info, let me know and
> we can go from there.
> Scott
> "New to MS SQL" wrote:
> > Is there an easy way to print out a simple text report of
> > all database names plus their internal database filenames
> > & logfile names plus the actual physical locations of each
> > file in SQL Server 2000?
> >
> > I'm an Oracle admin who's just inherited an SQL Server
> > full of multiple databases and I need to make a structural
> > diagram of what all lives where inside this server. In
> > Oracle, a simple sql script dumps out a text list of all
> > these kinds of things, but all I've been able to discover
> > thus far in MS SQL Enterprise Manager is an unfriendly GUI
> > interface that makes you have to repeatedly point, click,
> > and browse many times over and over again and again to get
> > this info one tiny piece at a time, which isn't very
> > efficient.
> >

List of all databases with their data & log files?

Is there an easy way to print out a simple text report of
all database names plus their internal database filenames
& logfile names plus the actual physical locations of each
file in SQL Server 2000?
I'm an Oracle admin who's just inherited an SQL Server
full of multiple databases and I need to make a structural
diagram of what all lives where inside this server. In
Oracle, a simple sql script dumps out a text list of all
these kinds of things, but all I've been able to discover
thus far in MS SQL Enterprise Manager is an unfriendly GUI
interface that makes you have to repeatedly point, click,
and browse many times over and over again and again to get
this info one tiny piece at a time, which isn't very
efficient.
New to MS SQL wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
Try this:
exec sp_MSforeachDB "sp_helpdb ?"
sp_helpdb by itself will give you basic database information for all
databases
sp_helpfile will give you the files used in the currently selected
database
Passing a database name to sp_helpdb gives both results and the
sp_MSforeachDB undocumented stored procedure automatically iterates
through the list of databases on the server and generates multiple
results sets.
David G.
|||Before you start getting bent out of shape over SQL Server, there an easy way
to accompish this. :-)
Open up Query Analyzer, select the Master database and type in the following
query:
select name, filename from sysdatabases
This will give you a quick list of all the database on the SQL Server
machine adn their physical location. If you want more info, let me know and
we can go from there.
Scott
"New to MS SQL" wrote:

> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
>
|||> select name, filename from sysdatabases
... and if on 2000, you can join sysaltfiles to get sizing information...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLScott" <SQLScott@.discussions.microsoft.com> wrote in message
news:CC55215A-B041-4370-8D87-8C4ABDBB0072@.microsoft.com...[vbcol=seagreen]
> Before you start getting bent out of shape over SQL Server, there an easy way
> to accompish this. :-)
> Open up Query Analyzer, select the Master database and type in the following
> query:
> select name, filename from sysdatabases
> This will give you a quick list of all the database on the SQL Server
> machine adn their physical location. If you want more info, let me know and
> we can go from there.
> Scott
> "New to MS SQL" wrote: