Get List of All Databases from SQL Server

In this short quick post we will learn how we can get the list of all user Database from SQL Server. SQL Server provided different system views those can be used to get information about objects present in SQL Server. To get list of all databases we can use sys.databases view that provides name of databases with other properties such as recovery model, state_desc,compatibility level etc.

The first four database are system database, the below query uses database_id>4, that will give us only user databases.

--Get List of User Database from SQL Server Instance
SELECT * FROM sys.databases
WHERE database_id>4



We can use these views to generate different type of queries. In below query I used sys.databases view to generate backup scripts for all the user databases.

--Generate Backup Scripts for all the user databases from sys.Databases view
SELECT 'BACKUP DATABASE ['+name+'] TO  DISK = N''C:\'+name+'_'+REPLACE(CAST(CAST(GETDATE() AS Date) AS VARCHAR(10)),'-','_') +'.bak''
WITH NOFORMAT, NOINIT,  NAME = N'''
+name+'-Full Database Backup'',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'

FROM sys.databases
WHERE database_id>4
               

1 comment:

  1. Free software may well have a comfortable environment and suit some types of business. Now it is quite convenient to use the secure online workflow presented on this site https://www.securedocsharing.com/ from pandadok. Here it is possible to send documents and files of any format for free, and also, for example, when using pandadok, it is possible to convert from one format to another. I recommend everyone to try the trial version of the program.

    ReplyDelete