Yesterday while I was writing script for SQL SERVER – 2005 – Find Unused Indexes of Current Database . I realized that I needed SELECT statement where I get the name of the current Database. It was very simple script.
SELECT DB_NAME() AS DataBaseName
It will give you the name the database you are running using while running the query.
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
113 Comments. Leave new
How to Get all the Database name & usage Size info
Respuesta obvia…
Thanks Pinal, u are the man!!
hello sir i want to know all the database name with predefined database in mysql plzzzzzz tell me
i eant to know all the fatabase names from fatabase
i want to know all the database names from database
My problem is I am trying to make code generic. I have 3 sites with their own servers. Site one has a Server referred to as Barcode as well as Site 2 and 3. These are all different server names and all have the same database name on them like Infocenter. When I write a stored procedure what I want to do is call a function against a UDT table with Servers Config. So on Site 1 Barcode is NC-XXX-DB at Site 2 Barcode is SA-XXX-DB. So instead of remember which stored procedures I need to change before I take them to site 2 I should be able to call the function. I can get the function to work but concatenating the server name to the database is not working. here is just a test string I was using. Does anyone have a suggestion at what I am doing wrong? Or would Dynamic SQL be better.
Select * from (Select Localization.dbo.GetServerName(‘BARCODE’)) Navision_intergration.dbo.Item_Update
Here is my scenario i have sets of 2 databases and a view from 1 database refers the other so whenever i make a change i have to go to all the sets and manually adjust it instead of just running a single script in all the sets. My question here is can i make the view dynamic so it does not matter which database i am refering to i tried the following but it did not work
Select * from Substring((select db_name),4,7) + ‘Domain’.dbo.Account
Thanx
Pinal I would appreciate it very much if you can share the script for the unused indexes.
Hello Amanuel,
You can know usase status of indexes from the result of sys.dm_db_index_usage_stats dmv.
Regards,
Pinal Dave
hello ,
can u help me plz….
i want to select from a table in a database in server1 and then insert the result in other table in different database
select name from table (server name1)
insert into table values() (server name2)
@Eman
Insert into Servername1.databasename1.ownername1.tablename
select * from Servername2.databasename2.ownername2.tablename2
This is called as four-part-name.
~ IM.
Excellent article!!!
Gof Bless you
You saved me lot of time during my project upgrade.
Great Work !!
how to select all database name from sql 2008 using query
rafique, the below is what I use. I wrote some if it, got bits and pieces from all over.
USE master
DECLARE d CURSOR FOR SELECT name FROM sysdatabases
WHERE name NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’) ORDER BY crdate, name
DECLARE @db varchar(255), @next_db varchar(255), @bak varchar(255), @USESQL NVARCHAR(max)
OPEN d
FETCH NEXT FROM d INTO @db
WHILE (@@FETCH_STATUS-1)
BEGIN
—-Enter here the stuff you want to run on each database
FETCH NEXT FROM d INTO @next_db
IF (@@FETCH_STATUS=-1) BREAK
SELECT @db=@next_db
END
DEALLOCATE d
Instead of writing your own cursor, use the undocumented SP called: sp_MSforeachdb
Example use: EXEC sp_MSforeachdb ‘PRINT ”?”’
Which will print the names of all your databases
Usage of undocumented procedures are not reliabel. They can be removed at any point of time
True.. But if you just want to do a quick query in all database and this SP is still working, I’d say, Go For It…
hello ,
can u help me plz….
i want to select from a table in a database in server1 and then insert the result in other table in different database
select name from table (server name1)
insert into table values() (server name2)
wow fantastic articl
can u help me plz
want to kno everything about sql without doing any studiezz
please summarize SQL books on line
than Q
regards
Thank you
Can I make a tag or something else to mark my database
if I want to use it in search for a specified database .
Something like :
SELECT [database name]
FROM “any where”
WHERE
“tag = [my value]”
Best regard.
Pinalkumar,
I want to know which of the two indexes is more reliable and effient (clustered and nonclustered).
Best Regard.
Pinal,
i need your help,
i want to take a value of one field from other database,
but the database name is variable (it defined in current database), and the last field result want to store to memvar @Result, suppose i make script like this:
DECLARE @Sql VARCHAR(200),
@DatabaseName_CRM VARCHAR(30),
@Result VARCHAR(500)
SET @DatabaseName_CRM = ‘CRM_MSCRM’
SET @Sql = ‘SELECT TOP 1 @Result = Address1_Line1 FROM ‘ + @DatabaseName_CRM +
‘.dbo.Account WHERE Address1_Line1 IS NOT NULL’
EXEC (@Sql)
— error :>
— Must declare the scalar variable “@Result”.
do you have any idea ?
Big Thanks
Boim
You need to use it via sp_executesql. Read about it in sql server help file.
In adition to all this i recomend what also Pinal Dave suggested. To know everything about a database or all of them use
sp_helpdb