SQL Serve 查询所有可用的数据库语句

--查询方式1
SELECT dtb.name AS [Database_Name] FROM master.sys.databases AS dtb 
WHERE (CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit)=0 
        and CAST(isnull(dtb.source_database_id, 0) AS bit)=0) 
ORDER BY [Database_Name] ASC


--查询方式2



SELECT  dtb.name AS [Database_Name] ,
        'Server[@Name='
        + QUOTENAME(CAST(SERVERPROPERTY(N'Servername') AS SYSNAME), '''')
        + ']' + '/Database[@Name=' + QUOTENAME(dtb.name, '''') + ']' AS [Database_Urn] ,
        CASE WHEN DATABASEPROPERTY(dtb.name, 'IsShutDown') IS NULL THEN 0x200
             ELSE 0
        END | CASE WHEN 1 = dtb.is_in_standby THEN 0x40
                   ELSE 0
              END | CASE WHEN 1 = dtb.is_cleanly_shutdown THEN 0x80
                         ELSE 0
                    END | CASE dtb.state
                            WHEN 1 THEN 0x2
                            WHEN 2 THEN 0x8
                            WHEN 3 THEN 0x4
                            WHEN 4 THEN 0x10
                            WHEN 5 THEN 0x100
                            WHEN 6 THEN 0x20
                            ELSE 1
                          END AS [Database_Status] ,
        dtb.compatibility_level AS [Database_CompatibilityLevel] ,
        dmi.mirroring_role AS [Database_MirroringRole] ,
        COALESCE(dmi.mirroring_state + 1, 0) AS [Database_MirroringStatus] ,
        dtb.recovery_model AS [RecoveryModel] ,
        dtb.user_access AS [UserAccess] ,
        dtb.is_read_only AS [ReadOnly] ,
        dtb.name AS [Database_DatabaseName2],
        dtb.create_date
        
FROM    master.sys.databases AS dtb
        LEFT OUTER JOIN sys.database_mirroring AS dmi ON dmi.database_id = dtb.database_id
WHERE   ( CAST(CASE WHEN dtb.name IN ( 'master', 'model', 'msdb', 'tempdb' )
                    THEN 1
                    ELSE dtb.is_distributor
               END AS BIT) = 0
          AND CAST(ISNULL(dtb.source_database_id, 0) AS BIT) = 0
        )
ORDER BY [Database_Name] ASC
go
USE [master]
go

--方法3
SELECT  dtb.name AS [Database_Name] ,
        CASE WHEN DATABASEPROPERTY(dtb.name, 'IsShutDown') IS NULL THEN 0x200
             ELSE 0
        END | CASE WHEN 1 = dtb.is_in_standby THEN 0x40
                   ELSE 0
              END | CASE WHEN 1 = dtb.is_cleanly_shutdown THEN 0x80
                         ELSE 0
                    END | CASE dtb.state
                            WHEN 1 THEN 0x2
                            WHEN 2 THEN 0x8
                            WHEN 3 THEN 0x4
                            WHEN 4 THEN 0x10
                            WHEN 5 THEN 0x100
                            WHEN 6 THEN 0x20
                            ELSE 1
                          END AS [Database_Status] ,
        dtb.compatibility_level AS [Database_CompatibilityLevel] ,
        dtb.user_access AS [UserAccess] ,
        dtb.is_read_only AS [ReadOnly] ,
        dtb.create_date
FROM    master.sys.databases AS dtb
        LEFT OUTER JOIN sys.database_mirroring AS dmi ON dmi.database_id = dtb.database_id
WHERE   ( CAST(CASE WHEN dtb.name IN ( 'master', 'model', 'msdb', 'tempdb' )
                    THEN 1
                    ELSE dtb.is_distributor
               END AS BIT) = 0
          AND CAST(ISNULL(dtb.source_database_id, 0) AS BIT) = 0
        )
ORDER BY [Database_Name] ASC
go
USE [master]
go

 

SQL2005以上查看表名以及说明

SELECT  Row_Number() over ( order by getdate() )  as 序号, case when a.colorder = 1 then d.name 
                   else '' end as 表名, 
        case when a.colorder = 1 then isnull(f.value, '') 
                     else '' end as 表说明
FROM syscolumns a 
       inner join sysobjects d 
          on a.id = d.id 
             and d.xtype = 'U' 
             and d.name <> 'sys.extended_properties'
       left join sys.extended_properties   f 
         on a.id = f.major_id 
            and f.minor_id = 0
 where a.colorder = 1 and d.name<>'sysdiagrams'