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

Information_Shema as a user in Master database

Hi, All
Recently, I was running a script like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
YifeiHi, All,
I think this is an issue for sql 2005, because the when I open em from db
server, the information_schema user does not show, but I connect it through
a remote machine that installed sql 2005, and the user showed.
same as another machine that did not run the script and did installed sql
2005.
Yifei
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:uVLA%23HI$FHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi, All
> Recently, I was running a script like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via
> group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the
> db
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the
> problem?
> any help is appreciated.
> Thanks
> Yifei
>
>

INFORMATION_SCHEMA: Probably bug in SQL-2005

Try to run this script, first in any database under SQL-2000, then in SQL-2005:

select COLUMN_DEFAULT from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_DEFAULT IS NOT NULL

In SQL-2000 values of the defaults are in single pair of brackets:

COLUMN_DEFAULT

('')
(0)
('')
(0)
(0)
(getdate())
(db_name())
(1)
(21)
(N'anonymous')

In SQL-2005 integer values are in double brackets, and all other defaults - in single brackets:

COLUMN_DEFAULT

('')
((0))
('')
((0))
((0))
(getdate())
(db_name())
((1))
((21))
(N'anonymous')

At the same time, OpenSchema method of ADO:

Connection.OpenSchema(adSchemaColumns)

returns different defaults in SQL2000 and SQL2005. In SQL2000 column defaults are in useful form, without any brackets:

'', 0, getdate()

But in SQL2005 they are enclosed in single brackets:

(''), (0), (getdate())

Does anybody know this problem? Is it normal, or it's a bug?

SQL Server 2005 normalizes expressions in defaults, check constraints and computed columns. As a result of this, the expression will look different from how it was stored in SQL Server 2000. However a particular expression will look the same irrespective of how you write it (extra spaces, paranthesis etc). This is documented in the following BOL topic:

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

|||

Now I understand!

Thanks!

INFORMATION_SCHEMA.SCHEMATA does not return all rows on SQL 2005

Hi,
In SQL 2000, the following query used to return all the database names:
SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
However, in SQL 2005, it just returns "master" as the database (that
too a number of times).
Can someone please confirm if this is a bug in SQL 2005?
Although I could use sp_catalogs_rowset;2, I prefer using ANSI SQL
standard statements.
Thank you in advance for your help.
Pradeep> In SQL 2000, the following query used to return all the database names:
Which is incorrect behavior. This is fixed in SQL Server 2005 (this should
never have been a list of databases).
To get a list of database names,
SELECT name FROM sys.databases|||To add on to Aaron's response, the reason for the SQL 2005 change was to
make the INFORMATION_SCHEMA.SCHEMATA view consistent with the ANSI standard.
The SQL 2000 behavior (database list) was proprietary. This is listed in
the SQL 2005 Books Online under the breaking changes topic
<ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/47edefbd-a09b-4087-937a
-453cd5c6e061.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Pradeep" <pradeep@.tapadiya.net> wrote in message
news:1136602519.887257.199350@.g43g2000cwa.googlegroups.com...
> Hi,
> In SQL 2000, the following query used to return all the database names:
> SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
> However, in SQL 2005, it just returns "master" as the database (that
> too a number of times).
> Can someone please confirm if this is a bug in SQL 2005?
> Although I could use sp_catalogs_rowset;2, I prefer using ANSI SQL
> standard statements.
> Thank you in advance for your help.
> Pradeep
>

INFORMATION_SCHEMA.ROUTINES LAST_ALTERED not changing

Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES NOT
changing when I perform a ALTER PROCEDURE statement?
When would it change?
tia,
ChrisIt's not fully implemented yet in sql2k. Expect to see such audit in sql2k5.
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:6E5F64BE-3373-4107-99C7-A54D48FA026D@.microsoft.com...
> Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES
> NOT
> changing when I perform a ALTER PROCEDURE statement?
> When would it change?
> tia,
> Chris

INFORMATION_SCHEMA.ROUTINES LAST_ALTERED not changing

Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES NOT
changing when I perform a ALTER PROCEDURE statement?
When would it change?
tia,
ChrisIt's not fully implemented yet in sql2k. Expect to see such audit in sql2k5.
--
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:6E5F64BE-3373-4107-99C7-A54D48FA026D@.microsoft.com...
> Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES
> NOT
> changing when I perform a ALTER PROCEDURE statement?
> When would it change?
> tia,
> Chrissql

INFORMATION_SCHEMA.ROUTINES LAST_ALTERED not changing

Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES NOT
changing when I perform a ALTER PROCEDURE statement?
When would it change?
tia,
Chris
It's not fully implemented yet in sql2k. Expect to see such audit in sql2k5.
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:6E5F64BE-3373-4107-99C7-A54D48FA026D@.microsoft.com...
> Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES
> NOT
> changing when I perform a ALTER PROCEDURE statement?
> When would it change?
> tia,
> Chris