SQL Server 取得資料庫所有資訊

--List DBNAME 取得該Server中所有DB名稱
select name
from master.dbo.sysdatabases


--List Table name 取得該DB中所有資料表名稱
SELECT 
 '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable
FROM sys.tables
   
   
 --List Column Property 取得所有資料表中的規格資料
 SELECT a.Table_schema +'.'+a.Table_name       as 表格名稱   
       ,b.COLUMN_NAME                          as 欄位名稱   
       ,b.DATA_TYPE                            as 資料型別   
       ,isnull(b.CHARACTER_MAXIMUM_LENGTH,'')  as 長度   
       ,isnull(b.COLUMN_DEFAULT,'')            as 預設值   
       ,b.IS_NULLABLE                          as 允許空值   
       ,( SELECT value   
                FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)   
               WHERE name='MS_Description'    
                 and objtype='COLUMN'    
                 and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME   
         ) as 欄位備註   
 FROM    
     INFORMATION_SCHEMA.TABLES  a
     LEFT JOIN INFORMATION_SCHEMA.COLUMNS b
               ON a.TABLE_NAME = b.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY a.TABLE_NAME