script to shows all dbs on all servers.

I am writing a script that would give me databases of all servers. I also
need to know whats the database size fo each database on each server. The
way i get teh first one working was set up linked server to all databases
that i need the information from. Then for each server get the databases
from sysdatabases. I run into problem when i need to know the database
size. sp_spaceused returns 2 rows for a database is there any procedure
that i can use in msforeachdb to get the database size. Or is there nay
other means to do this. any help will be greatly appreciated.
TIA
this is the text for my existing proc.
CREATE PROCEDURE dbo.PR_ALLDBSOFALLSERVERS
AS
DECLARE @.ServerName varchar(100),
@.return int,
@.sql nvarchar(1000)
SET NOCOUNT ON
if exists (select * from master.dbo.sysobjects where name = 'Details')
BEGIN
truncate table master..Details
end
else
begin
Create Table Master..Details
( ServerName Char(100),
DatabaseName Char(100))
end
DECLARE server_cursor CURSOR
FOR
SELECT srvname FROM master..sysservers
where srvname not in ('repl_distributor')
OPEN server_cursor
FETCH NEXT FROM server_cursor INTO @.ServerName
WHILE (@.@.fetch_status <> -1)
BEGIN
SELECT @.sql = 'INSERT INTO master..Details SELECT ''[' + rtrim(@.ServerName)
+']'', name from [' + rtrim(@.Servername)+'].master.dbo.sysdatabases'
--select @.sql
exec sp_executesql @.sql
FETCH NEXT FROM server_cursor INTO @.ServerName
END
DEALLOCATE server_cursor
SELECT * from master..Details
SET NOCOUNT OFF
GO
Message posted via http://www.webservertalk.comKilobytes of all files used by the current database (data and log files):
SELECT SUM([size]) * 8 FROM dbo.sysfiles
sysfiles.size is the number of pages used by the db; each page is 8 kb; so
size * 8 = kb. multiply by 1024 to get bytes, or divide by 1024 to get mb,
etc.
"ishaan99 via webservertalk.com" wrote:

> I am writing a script that would give me databases of all servers. I also
> need to know whats the database size fo each database on each server. The
> way i get teh first one working was set up linked server to all databases
> that i need the information from. Then for each server get the databases
> from sysdatabases. I run into problem when i need to know the database
> size. sp_spaceused returns 2 rows for a database is there any procedure
> that i can use in msforeachdb to get the database size. Or is there nay
> other means to do this. any help will be greatly appreciated.
> TIA
> this is the text for my existing proc.
> CREATE PROCEDURE dbo.PR_ALLDBSOFALLSERVERS
>
> AS
> DECLARE @.ServerName varchar(100),
> @.return int,
> @.sql nvarchar(1000)
> SET NOCOUNT ON
> if exists (select * from master.dbo.sysobjects where name = 'Details')
> BEGIN
> truncate table master..Details
> end
> else
> begin
>
> Create Table Master..Details
> ( ServerName Char(100),
> DatabaseName Char(100))
> end
> DECLARE server_cursor CURSOR
> FOR
> SELECT srvname FROM master..sysservers
> where srvname not in ('repl_distributor')
> OPEN server_cursor
> FETCH NEXT FROM server_cursor INTO @.ServerName
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> SELECT @.sql = 'INSERT INTO master..Details SELECT ''[' + rtrim(@.ServerName
)
> +']'', name from [' + rtrim(@.Servername)+'].master.dbo.sysdatabases'
> --select @.sql
> exec sp_executesql @.sql
> FETCH NEXT FROM server_cursor INTO @.ServerName
> END
> DEALLOCATE server_cursor
> SELECT * from master..Details
> SET NOCOUNT OFF
> GO
> --
> Message posted via http://www.webservertalk.com
>sql

Script to rename database (and files)

I need to rename several databases and also rename the data and log files
(since these filenames contain the DB name). I was thinking of writing a
script to do so but before I did, I thought I would ask if anyone had such a
script. If someone happens to have something like this - I would greatly
appreciate it.
Thanks in advance.Hi,
To rename the logical name of the file:- use the below script
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
='NEW_MDF_NAME')
Do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
To rename the physical files. Usae the below script sample.
sp_detach_db <dbname>
go
--rename the LDF file and MDF physical files using windows explorer
go
sp_attach_db <Dbname> ,'phsical_mdf_with_path','new_ldf_with_p
ath'
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> I need to rename several databases and also rename the data and log files
> (since these filenames contain the DB name). I was thinking of writing a
> script to do so but before I did, I thought I would ask if anyone had such
a
> script. If someone happens to have something like this - I would greatly
> appreciate it.
> Thanks in advance.
>|||... also you can use sp_renamedb to rename a database, but follow Hari's so
lution to change the logical & physical filenames.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Perform
ance topic.
"Hari Prasad" wrote:

> Hi,
> To rename the logical name of the file:- use the below script
> alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
> ='NEW_MDF_NAME')
> Do the same for LDF file as well.
>
> For renaming the database.
> alter database OLD_DB_NAME modify name = new_db_name
>
> To rename the physical files. Usae the below script sample.
> sp_detach_db <dbname>
> go
> --rename the LDF file and MDF physical files using windows explorer
> go
> sp_attach_db <Dbname> ,'phsical_mdf_with_path','new_ldf_with_p
ath'
> --
> Thanks
> Hari
> MCDBA
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> a
>
>

Script to rename database (and files)

I need to rename several databases and also rename the data and log files
(since these filenames contain the DB name). I was thinking of writing a
script to do so but before I did, I thought I would ask if anyone had such a
script. If someone happens to have something like this - I would greatly
appreciate it.
Thanks in advance.
Hi,
To rename the logical name of the file:- use the below script
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
='NEW_MDF_NAME')
Do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
To rename the physical files. Usae the below script sample.
sp_detach_db <dbname>
go
--rename the LDF file and MDF physical files using windows explorer
go
sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_pat h'
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> I need to rename several databases and also rename the data and log files
> (since these filenames contain the DB name). I was thinking of writing a
> script to do so but before I did, I thought I would ask if anyone had such
a
> script. If someone happens to have something like this - I would greatly
> appreciate it.
> Thanks in advance.
>
|||... also you can use sp_renamedb to rename a database, but follow Hari's solution to change the logical & physical filenames.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hari Prasad" wrote:

> Hi,
> To rename the logical name of the file:- use the below script
> alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
> ='NEW_MDF_NAME')
> Do the same for LDF file as well.
>
> For renaming the database.
> alter database OLD_DB_NAME modify name = new_db_name
>
> To rename the physical files. Usae the below script sample.
> sp_detach_db <dbname>
> go
> --rename the LDF file and MDF physical files using windows explorer
> go
> sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_pat h'
> --
> Thanks
> Hari
> MCDBA
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> a
>
>

Script to rename database (and files)

I need to rename several databases and also rename the data and log files
(since these filenames contain the DB name). I was thinking of writing a
script to do so but before I did, I thought I would ask if anyone had such a
script. If someone happens to have something like this - I would greatly
appreciate it.
Thanks in advance.Hi,
To rename the logical name of the file:- use the below script
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
='NEW_MDF_NAME')
Do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
To rename the physical files. Usae the below script sample.
sp_detach_db <dbname>
go
--rename the LDF file and MDF physical files using windows explorer
go
sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_path'
--
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> I need to rename several databases and also rename the data and log files
> (since these filenames contain the DB name). I was thinking of writing a
> script to do so but before I did, I thought I would ask if anyone had such
a
> script. If someone happens to have something like this - I would greatly
> appreciate it.
> Thanks in advance.
>|||... also you can use sp_renamedb to rename a database, but follow Hari's solution to change the logical & physical filenames.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hari Prasad" wrote:
> Hi,
> To rename the logical name of the file:- use the below script
> alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
> ='NEW_MDF_NAME')
> Do the same for LDF file as well.
>
> For renaming the database.
> alter database OLD_DB_NAME modify name = new_db_name
>
> To rename the physical files. Usae the below script sample.
> sp_detach_db <dbname>
> go
> --rename the LDF file and MDF physical files using windows explorer
> go
> sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_path'
> --
> Thanks
> Hari
> MCDBA
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> > I need to rename several databases and also rename the data and log files
> > (since these filenames contain the DB name). I was thinking of writing a
> > script to do so but before I did, I thought I would ask if anyone had such
> a
> > script. If someone happens to have something like this - I would greatly
> > appreciate it.
> >
> > Thanks in advance.
> >
> >
>
>

script to kick all users off database

I am writing a script to delete all user tables from a SQL Server 2000
database and before I do that I want to kick all other users off the
database.
Can anyone give me the sql script that will do this?
Killing all the connections to the database you can use command:-
ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Vishal,
That also puts it in single user mode, which might not be desirable.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Vishal Parkar wrote:
> Killing all the connections to the database you can use command:-
> ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
>
|||Caroline,
This is what I use:
CREATE proc sp_dba_killscript
@.dbname sysname
as
select 'kill '+ convert(varchar(5),a.spid)
from master..sysprocesses a join master..sysdatabases b
on (a.dbid=b.dbid)
where b.name = @.dbname
Paste the result into your query window and run it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Caroline wrote:
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?
|||There is a nice script that Tibor wrote, that will send a message to
everyone logged in to the server. So you can before killing... His web site
is www.dbmaint.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Caroline" <carolinefryer@.gmail.com> wrote in message
news:eea6e29c.0411091656.1ee19f46@.posting.google.c om...
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?

script to kick all users off database

I am writing a script to delete all user tables from a SQL Server 2000
database and before I do that I want to kick all other users off the
database.
Can anyone give me the sql script that will do this?Killing all the connections to the database you can use command:-
ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Vishal,
That also puts it in single user mode, which might not be desirable.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Vishal Parkar wrote:
> Killing all the connections to the database you can use command:-
> ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
>|||Caroline,
This is what I use:
CREATE proc sp_dba_killscript
@.dbname sysname
as
select 'kill '+ convert(varchar(5),a.spid)
from master..sysprocesses a join master..sysdatabases b
on (a.dbid=b.dbid)
where b.name = @.dbname
Paste the result into your query window and run it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Caroline wrote:
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?|||There is a nice script that Tibor wrote, that will send a message to
everyone logged in to the server. So you can before killing... His web site
is www.dbmaint.com
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Caroline" <carolinefryer@.gmail.com> wrote in message
news:eea6e29c.0411091656.1ee19f46@.posting.google.com...
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?sql

script to kick all users off database

I am writing a script to delete all user tables from a SQL Server 2000
database and before I do that I want to kick all other users off the
database.
Can anyone give me the sql script that will do this?Killing all the connections to the database you can use command:-
ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Vishal,
That also puts it in single user mode, which might not be desirable.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Vishal Parkar wrote:
> Killing all the connections to the database you can use command:-
> ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
>|||Caroline,
This is what I use:
CREATE proc sp_dba_killscript
@.dbname sysname
as
select 'kill '+ convert(varchar(5),a.spid)
from master..sysprocesses a join master..sysdatabases b
on (a.dbid=b.dbid)
where b.name = @.dbname
Paste the result into your query window and run it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Caroline wrote:
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?|||There is a nice script that Tibor wrote, that will send a message to
everyone logged in to the server. So you can before killing... His web site
is www.dbmaint.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Caroline" <carolinefryer@.gmail.com> wrote in message
news:eea6e29c.0411091656.1ee19f46@.posting.google.com...
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?

Script to copy entire table

I'm a newbie to script writing. I'm trying to write a script to copy all
data from a table to the same table in a 2nd database. Both databases are
on the same server and are identical in design. I can do this with DTS but
wanted a script I could email to a user to run in Query Analyzer.

Example:
Copy entire table called 'Customers' in the 'Data01' database to
table 'Customers' in the 'Data02' database

I want to overwrite all data in the destination table.

Thanks"sqlnewbie" <sqlnewbie@.yahoo.com> wrote in message
news:k4PYb.27374$vs5.13502@.newssvr25.news.prodigy. com...
> I'm a newbie to script writing. I'm trying to write a script to copy all
> data from a table to the same table in a 2nd database. Both databases are
> on the same server and are identical in design. I can do this with DTS
but
> wanted a script I could email to a user to run in Query Analyzer.
> Example:
> Copy entire table called 'Customers' in the 'Data01' database to
> table 'Customers' in the 'Data02' database
> I want to overwrite all data in the destination table.
> Thanks

/* Replace all data in the destination table */

use Data02
go

truncate table dbo.Customers
insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers

/* Insert only data which isn't already there */

use Data02
go

insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers c1
where not exists (select *
from dbo.Customers c2
where c1.PrimaryKeyCol = c2.PrimaryKeyCol)

Note that TRUNCATE TABLE requires certain permissions (see Books Online),
and won't work if the table is referenced by foreign keys. In this case, you
can use "DELETE FROM dbo.Customers".

I would be careful about sending scripts to users, as they often seem to run
them in the wrong place at the wrong time - moving data should really be a
DBA's task (although I appreciate that not everyone has a DBA available).
You may want to back up the database first, just in case.

Simonsql

Script for Publishing

Can the script files be written in any .net language? Writing code in VB
makes me ill.You could write a C# custom app using SOAP API.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"?" <?@.discussions.microsoft.com> wrote in message
news:A37994BD-2958-457F-9C93-295B93406201@.microsoft.com...
> Can the script files be written in any .net language? Writing code in VB
> makes me ill.

Scope_Identity with @Return_Value

I am currently writing an application that inserts data into a MS SQL Server 2000 DB. I am developing the application in Microsoft Visual Web Developer 2005 EE. So far everything has come together nicely. However, I have run into a situation that I can't seem to figure out.

I wrote a stored procedure to insert data into the DB from a Web Form. The insert works nicely; however, one of the requirements I have is that I would like the DB to return the last row inserted into to my table. I am using theScope_Identity() function at the end of my stored procedure to accomplish this task:

...Last few lines of Stored Procedure...

@.ANNEXCITYCOUNCILACTION,
@.CRA
)

RETURN SCOPE_IDENTITY()

GO

I have conducted several tests on the SP, and received the following output from the DB when it is run:

(1 row(s) affected)
(0 row(s) returned)
@.GID = <NULL>
@.RETURN_VALUE = 116
Finished running [dbo].[usp_InsertProject].

I would like to take the result of the @.RETURN_VALUE (116), and create a variable that I can use to embed into an e-mail. The code for the e-mail generation is working however, the value that comes through for the @.RETURN_VALUE is always 0. I have tried several different things to get this to work, but so far no luck.

Here is the application code I am using to create the e-mail:

Sub SendEmail()

Dim mySqlDataSource1 = SqlDataSource1.ConnectionString.ToString

Dim myConnection As New Data.SqlClient.SqlConnection(mySqlDataSource1)
Dim myCommand As New Data.SqlClient.SqlCommand("usp_InsertProject", myConnection)

myCommand.CommandType = Data.CommandType.StoredProcedure

Dim parameterGID As New Data.SqlClient.SqlParameter("@.RETURN_VALUE", Data.SqlDbType.Int)
parameterGID.Direction = Data.ParameterDirection.ReturnValue

myCommand.Parameters.Add(parameterGID)

Dim reader As Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()

Dim GID As Integer = CInt(parameterGID.Value)
GID.ToString()

...E-mail code is below this, but is working, so not included ...

End Sub

I would like to insert the GID variable into the e-mail, but for some reason it won't work. The following error occurs when the InsertCommand is invoked:

ExecuteReader requires an open and available Connection. The connection's current state is closed.

You have two problems from what I can see. The first is indicated in the error. Run myConnection.Open() before you run a command against the database. Second is that you're running ExecuteReader(), but you don't seem to be using the data reader. If you're using it below, please ignore. If you aren't, you need to be running ExecuteNonQuery() instead.|||

Hi Chris,

Thanks for the help...I modifed the code, and added:

Email Sub changes:
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

Stored Procedure changes:
@.CRA
)

SELECT
@.GID = @.@.Identity
GO

The results of the Stored Procedure are now:

(1 row(s) affected)
(0 row(s) returned)
@.GID = 127
@.RETURN_VALUE = 0
Finished running [dbo].[usp_InsertProject].

I now have an output parameter with the correct value I would like to set as a variable in my code.

However, the InsertCommand is being executed from a FormView, and is using a SqlDataSource to access the DB. When I put myConnection.Open() into the Sub it gives me the following error:

Error executing 'InsertCommand' in SqlDataSource 'SqlDataSource1'. Ensure the command accepts the following parameters: @.CREATEDBY, ...

The Sub is being run on the InsertButton_Click event. Is there a way to reference the open connection that the SqlDataSource object already has open? This way I can grab the @.GID value...

|||

If you want to use a sqldatasource for the insert, that's fine. Not knowing exactly how your database is set up, and what you can/can not change, it seems to me that what you want is:

Create a stored procedure that takes all your values, and returns a last inserted id. If you can not change the current stored procedure, create a new one that takes the same parameters, calls the original stored procedure then issues the T-SQL command RETURN SCOPE_IDENTITY(). If you do not already have a return value parameter set up for sqldatasource1's insert command, add one now (Or use the wizard to add all the parameters automatically, and it'll create @.RETURN_VALUE for you).

Inside the sqldatasource1_Inserted event (It must be done here), add code that looks like:

dim MyID as integer=e.command.parameters("@.RETURN_VALUE")

SendEmail(MyID)

Then remove all your myConnection/myCommand code, and change your SendEmail procedure to accept the id as a parameter.

|||

I got it to work by using the following code:

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted

Dim myID As Integer

myID = e.Command.Parameters("@.GID").Value

myID.ToString()

Dim Email As New Net.Mail.MailMessage()

E-mail code below this...

The key to getting it to work was putting it in the SqlDataSource1_Inserted event. This allowed me to get the output parameter.

Thanks for your help Motley...You pointed me in the right direction, and helped me out tremendously!