Listing Size Of All User Databases In SQL2005
Aug 9, 2007Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?
Thanks,
ADVERTISEMENT
Listing Recovery Modes On Databases With SP Or T-S
Mar 20, 2007I need to display the recovery mode of ALL databases on a server into a table. (Just like the sp_helpdb prcedure extended with the recoverymodel of each Database).
Can anyone give me a hint on this or provide me with a little tsql code snipplet to realize this?
Thanks alot in advance
Listing SQL Server Databases On A Network
Dec 7, 2005I would like to provide users of a client program a list of databases on thenetwork that match a certain pattern, like "%frp%". I have seen a list ofdatabases in the Windows XP ODBC configuration tool. What I would like toknow is can I get that information through a query to one of the databases,or is there a control or application on the client machine that I can callto get the list?Does the thread "Databases not showing up in Enterprise Manager" refer towhat I am trying to do?Best regards,Steve Caldwell
View 5 Replies View RelatedSQL Server 2014 :: Query For Listing Views In A Database Involving Tables From Other Databases?
Oct 19, 2015script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies View RelatedSQL 2012 :: Listing User Created Objects?
Oct 7, 2015I'm trying to list everything (tables, view, procedures, functions, etc.) that was created by users in a database.
The query which seems to eliminate the most SQL system type objects is shown below.
SELECT *
FROM sys.all_objects SAO
WHERE SAO.is_ms_shipped = 0
order by SAO.type, SAO.name
This still includes some non-user created objects, like the below. See the attachment for details.
fn_diagramobjects
sp_alterdiagram
sp_creatediagram
How can I get rid of these type of objects without filtering on SAO.name LIKE...
How To Setup The User Access Right To A BackupOperator User In SQL2005.. Thanks
Nov 21, 2007I would like the Backup SQL user A can backup and restore DATABASE_a, what access right I need to grant to him. Although he can backup the database_a to the c:Program Files.....Database_a.BAK but he cannot restore it with some message said ''not enough security privieges..
Please quote some example right assigment so that I can replicate to our environment
Many Thanks...
SQL2005 Data Import Error, Unicode Data Is Odd Byte Size For Column &&<n&&>. Should Be Even Byte Size
Aug 23, 2006Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,
Any help on this greatly appreciated...
ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
Many thanks
List Databases With Their Size
Feb 12, 2007I want to list my databases, and physical related files, and their size but the following commands do not provide the size. Any idea?
SELECT * FROM sysdatabases
SELECT * FROM sys.sysfiles
I know it's silly question
Canada DBA
Size Of SQLServer Databases With Indices
Jul 20, 2005Hello,I am starting to examine using SQLServer instead of Oracle. I wentthrough the SQLServer import utility to copy tables from Oracle intoSQLServer. I then checked the size of the database.I then started to specify indices for the tables. I did not see thedatabase file size grow all that much. I kept on using the shrinkcommand to make sure that wasted space was removed. I even made surethat minimal free space was allowed.I was rather expecting (from experience with Oracle) that there wouldbe a large growth in the database when addind indices. So what did Imiss? Are the indices stored elsewhere and I missed them? DoesSQLServer handle indices differently so they dont' bloat the databasesize?The size of our data in SQLServer is a key factor in whether we movefrom Oracle, so I need to under how indices effect storage size.Thanks for any help.Bart TorbertJoin Bytes!
View 7 Replies View RelatedSQL2005 User Id Naming Issue?
Dec 7, 2006Hey all
Am using SSMS to add users to a DB and here is an issue I cant conquer.
User id's in this format "Aname" work as designed
User Id's in this format "John P User" will not be recognized by ssms as valid windows user names even though "John P User" is Administrator of the local computer or even Domain Administrator. I have tried a script to enter the name and still the multiname format errors out where the normal naming format works every time..
Im new to the forum and very green in this arena, I have scanned 43 pages looking for a similar issue and have found nothing even remotely similar in a post.
I would be thankful for any bones you wise people could throw my way.
Best: Phil
HOw Can I Alter Databases To Change Log Size To Unlimited?
Feb 17, 2008 We have 300+ databases on one sinlge server. If I need to change log size to "unlimited" for all of them, is there any way to do so? Please advice.
-Julie
How Can A User Change Password In SQL2005 Via TSQL?
Mar 30, 2007I have an application that controls user logins, passwords, etc. at the front end for a SQL database. I am in the stage of migrating to SQL2005 and cannot get the TSQL code to allow a user to change their own password. Here's the background;
The ADMIN of the app is a Sysadmin on the SQL server and can create logins, set roles, etc. Assume the Admin creates a user TOM with a password of xxxx. This works fine using the create login statement from a Connect.Execute statement from my app like so;
"Create Login 'TOM' With Password 'xxxx', Default_Database = 'myDB', Check_Policy = OFF"
TOM will be setup with db roles as well
When TOM logins into my app, he will have to change his password at some point. The TSQL code I am using (which fails) is executed by TOM who has a connection to the SQL db because he is logged into the app.
"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx', Check_Policy = OFF"
At this point I get an error:
RunTime error -2147217900 (80040e14)
ODBC SQL Server Driver][SQL Server] Cannot alter
the login 'TOM', becuase it does not exist or you do
not have permission.
Obviously it exists since TOM is currently logged into the SQL Server. So if it's permissions related, what permissions does a user need to change his/her password? Or is there another way to do it?
Thanks in advance for your help.
CH
Implementing Multiple Databases Due To Huge Data Size?
Aug 30, 2005Has anyone implemented split data for an application between two databases because the data size is extremely large? If so could you please point me to relevant information.In this split data scenario, a table will automatically carry over to another database whenever the size limit for the current database is reached. The challenge is here for the DAL (data access layer) to automatically look into the appropriate database when the next row of data is in another database. OR Perhaps there is another solution to this terasize data problem..Any help on this would be greatly appreciated.
View 8 Replies View RelatedAdding Size Of Identified Tables On All Databases On A Servers??
Jun 13, 2000I have used the following script to add the size of chosen tables in all the databases on a given server. What I need to do is to create a GRAND total. Can someone give me a hint?
exec sp_msforeachdb
@Command1 = "print'?'",
@Command2 = "use ?
SELECT CAST (o.name as char(20)) as 'Table', SUM(c.length) 'Record size',
MAX (i.rows) '#of rows',
CONVERT (decimal (10, 4), SUM (c.length * i.rows)/(1024.00 * 1024.00)) 'Approx. size (MB)'
FROM sysobjects o, syscolumns c, sysindexes i
WHERE o.id = c.id
AND o.id = i.id
AND (i.indid = 1 or i.indid = 0)
AND o.type = 'U'
And o.name in ('Table1','Table2','Table3')
GROUP BY o.name
COMPUTE SUM (CONVERT (decimal (10,4), SUM (c.length * i.rows)/(1024.00 * 1024.00)))"
Thank you
SQL Server 2012 :: MDF And LDF Size For Spreadsheet - Multiple Databases
Aug 28, 2015I need a script that will return the mdf & ldf for multiple databases.
I am currently running...
sp_helpdb 'TestDataname'
...and copying the size of the mdf and ldf into an excel spreadsheet.
How can I get the mdf AND ldf file size for all of the databases in an instance? I need the MDF and LDF seperated and I want the actual size of the file as it appears on the file system.
Temporary Table Not Dropped After User Disconnected (SQL2005)
Oct 7, 2005Hello all,
View 12 Replies View RelatedSQL2005: Running TSQL Commands In Single-user Mode
Dec 20, 2007I'm having trouble running TSQL commands when SQL2005 is in single-user mode.I've restarted SQL with -m, -c, -T3608 set in the startup options.I can get into Config manager OK, but as soon as I try to start a NewQuery it complains that the DB is in single-user mode and there'salready someone connected to the DB.Is there something I need to turn off? If not, how am I supposed torun queries in single-user mode? What I want to do is move the modeland msdb databases but I can't run the commands to do this until I getpast this problem.Thanks for any assistance you can give on this.Mike
View 6 Replies View RelatedSql2005 Express 'windows NT User Or Group Not Found. Check The Name Again.
Feb 26, 2006I have installed visual studio 2005 with sql express. My tutorial book asked me to do the following to be able to use sql.
sqlcmd -S mycomputernameSQLExpress -E
which worked and then I get
1>
where I am to put in
1> sp_grantlogin [mycomputernamemyusername]
2> go
after this i get the error.
Msg 15401, Level 11, State 1, Server mycomputernameSQLEXPRESS, Procedure sp_grantlogin, Line 49
Windows NT user or group 'mycomputernamemyusername' not found. Check the name again.
I used Local System for the user name as that is what I think I am seeing as the right user account.
Help?
Failed DTS Package Locks User Out Of SQL2005 (Windows Authentication) ... For Good!
Feb 13, 2006Would be interested in any advice or comment on the issue we are experiencing with SQL 2005.
In order to test some DTS package migrations, we simply created a DTS package on SQL 2000 (using Export) to copy all database objects from one database to another.
Then we restored the source database on SQL 2005 server, migrated the DTS package, and tried to execute it.
The package migrated 'fine', ran part way, and then failed. Since then we have been unable to connect to the server (Database Engine, or Integration Services) using Windows Authentication. (Error 18456, Sev 16, State 11).
We can connect to the Database Engine using SQL Authentication (but not to Integration Services which only allows Windows Authentication).
We have been able to replicate this consistently - every annoying time we've tried it!
(Reinstalling SQL Server does 'fix' the issue ... until you try a similar package again).
Restore A Set Of Databases From A SQL2000 Server To A SQL2005 Server
Nov 3, 2006
I can restore databases one by one, setting .mdf and .ldf destination paths.
How can I restore all my Databases at the same time?
Thanks
G. Zanghi
Size Of User Tables In A Database
Nov 4, 2003The following procedure will display the size of all the user tables in a database.
CREATE proc sp_tablesize
as
if exists (select * from sysobjects where name = 'sp_tablesize')
begin
goto calculate_tablesize
end
else
begin
CREATE TABLE #SpaceUsed
(
TableName sysname,
TableRows int,
TableSize varchar(10),
DataSpaceUsed varchar(10),
IndexSpaceUsed varchar(10),
UnusedSpace varchar(10)
)
goto calculate_tablesize
end
calculate_tablesize:
declare @tablename nvarchar(50)
declare @cmd nvarchar(50)
declare c1 cursor for select name from sysobjects where xtype='u'
open c1
fetch c1 into @tablename
while @@fetch_status = 0
begin
set @cmd='exec sp_spaceused['+@tablename+']'
insert into #SpaceUsed exec sp_executesql @cmd
fetch next from c1 into @tablename
end
select * from #SpaceUsed
drop table #SpaceUsed
deallocate c1
How Can I Rename One Of My User Databases In SQL?
Apr 20, 2001How can I rename one of my user databases in SQL?
View 2 Replies View RelatedUser Can See All Databases SQL 2005
Mar 20, 2008I have a database user with rights on one database. If he connects with management studio 2005 he can see all databases.
How to change that he can only see database where he has rights on viewing and/or changing?
Henri
~~~~
There's no place like 127.0.0.1
Grant Permissions In All User Databases
Jan 19, 2001Hello together,
can anybody help me. I'm looking for an easy way to grant permissions to a user in all user databases. I already have a script which grants permission to all views and userdefined tables within one database, but since I have to run it in about 100 databases it's still quite timeconsuming.
Is there a way to execute that script in all user databases at once ???
Markus
Identifying System/User Databases
Nov 12, 2002Hi,
i´m looking for TSQL-Code (7.0/2000) to identify, if a SQL Server Database ist a SystemDatabase or a UserDatabase. In the sysdatabases there is no information abount that.
Skol,
Peter
Managing User Access To Databases
Aug 21, 2002Scenario:
1. Access to database objects is based on database roles.
2. Application administrators handle the assignment of users to database roles. Application administrators are first line managers, typically; they are not DBA's and not Help Desk staff.
Question:
What products are available to simplify the management of user access to databases in the aforementioned scenario?
TIA to all respondents.
--
Peter
List All Databases That A User Has Access To
Nov 17, 2014Any way to list all databases that a user has access to?
View 6 Replies View RelatedSelecting Only Databases In Which User Has A Role
May 31, 2006I have a user in SQL Server 2000 with public, datareader and datawriter roles on several databases. I need to select all those databases, how can I do that. I have tried sp_databases but I get ALL databases. I also tried sp_MShasdbaccess but I still get all databases.
View 4 Replies View RelatedList All Databases That A User Has A Login
Nov 26, 2007Hi everyone,
I have an instance with many databases in it. I am looking for the easiest way to see which of those databases a user has a login on. What is the most efficient way of doing this?
Thanks,
Anil
Moving Some User Databases To New Server
Apr 10, 2008Hi!
I have a SQL 2000 server with a lot of databases (about 100) and I would like to move a great deal of them (+/- 50) to a new SQL 2005 server. All the databases I would move has the same word in the database name. Let's say the all include "Customer" in the Database name.
It would be a huge job to detach and attach every db manually. Is there a method that can detach db's based on a query againts the master db with a select on the name of the db?
If so, I also need to attach the db's on the new server with a script after moving the files to a new location.
Any good ideas?
With User Sa I Can See Mot Databases How Can The Rest Of The Users See Them All?
Jun 26, 2007I have a third party application that creates several databases on my sql server. This party uses the same user for accessing all this databases. However after moving to a new server this databases the user is no longer able to query them whith the exception of the "sa" user. Could anyone explain me how to add permissions to this databases manually so the user can query them again.
Thanks in advance
brgds
Miguel
How To Find Out Which Databases A User Have Access To?
Apr 3, 2007Hi, my first question, tried to use search first but couldnät find what I need.
Hopefully someone has asuggestion.
I work for a ISV producing HR applications.
When user starts the applications the program shows all databases that the user have access to. This is done by select all names from master..sysdatabases and then trying to "USE dbname" to see if user have access,. Users doesn't have sa rights.
This procedure takes approximately 90 secs for a server with 500 databases and that has become a problem.
Does anyone know off a faster method, any suggestions?
/Regards Anders
Size Limit On CLR Extension User Defined Types
Jun 10, 2006
Why is there a limit of 8000 bytes on CLR Extension user defined types in SQl Server 2005?
We now have varchar(max), varbinary(max) and XML data types that are unlimited in size, byt UDT's are limited to 8000 bytes!.
This limitation is ruining a key project of mine!
Any ideas that MS may lift this limit?
Regards
Derek