# SQL

## MS SQL

### 查詢 DB 整個 Schema 的欄位

select  t.[name]            'Table',
        c.[name]            'Column',
        case when d.[name] like '%char' then d.[name] + '(' + cast( c.max_length / 2 as nvarchar) + ')' else d.[name] end 'Data Type',
        iif(c.[is_identity] = 1 ,'PK','') 'Is Id',
        iif(c.[is_nullable] = 1 ,'O','') 'Is Nullable',
		'' note,
		case when fk.object_id is not null then '>-' else null end as rel,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
        pk_col.name as pk_column_name,
        fk_cols.constraint_column_id as no,
        fk.name as fk_constraint_name
from        sys.schemas s 
inner join  sys.tables  t on s.schema_id = t.schema_id
inner join  sys.columns c on t.object_id = c.object_id
inner join  sys.types   d on c.user_type_id = d.user_type_id 
left outer join sys.foreign_key_columns fk_cols on fk_cols.parent_object_id = t.object_id and fk_cols.parent_column_id = c.column_id 
left outer join sys.foreign_keys fk             on fk.object_id = fk_cols.constraint_object_id
left outer join sys.tables pk_tab               on pk_tab.object_id = fk_cols.referenced_object_id
left outer join sys.columns pk_col              on pk_col.column_id = fk_cols.referenced_column_id and pk_col.object_id = fk_cols.referenced_object_id

### 確認DB空間

SELECT DB_NAME(database_id) AS DatabaseName,
       Name AS Logical_Name,
       Physical_Name,
       (size * 8) / 1024 SizeMB
FROM sys.master_files 
WHERE DB_NAME(database_id) = 'MY_DB'

### 確認Table空間

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00)/1024, 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00)/1024, 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

### 針對特定store procedure使用欄位查詢

-- 搜尋預存程序定義中是否包含特定字串
SELECT
     [Scehma] = SCHEMA_NAME(o.schema_id)
   ,o.Name
   ,o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
     ON o.object_id = m.object_id
WHERE m.definition LIKE '%TableName%';

## Oracle

### Table 被用到哪一個store procedure或package

select distinct owner, name, type 
from all_source 
where upper(text) like '%StoreProcedure%' 
order by owner, name, type

### 已經確定在哪個schema (user account)下可用user_source

select distinct name, type 
from user_source 
where upper(text) like '%user account%' 
order by name, type

### 查詢Table中的欄位

select table_name 
from all_tab_columns 
where column_name = 'PICK_COLUMN';

### 列出特定Schema下所有資料表及欄位型別、長度、精準位數、NULLABLE、預設值

SELECT
  C.OWNER, C.TABLE_NAME, C.COLUMN_ID, C.COLUMN_NAME, 
  DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_DEFAULT, 
  NULLABLE, COMMENTS
FROM
  ALL_TAB_COLUMNS C 
JOIN ALL_TABLES T ON 
  C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN ALL_COL_COMMENTS R ON
  C.OWNER = R.Owner AND 
  C.TABLE_NAME = R.TABLE_NAME AND 
  C.COLUMN_NAME = R.COLUMN_NAME
WHERE  
  C.OWNER  = 'DBAccount'
ORDER BY C.TABLE_NAME, C.COLUMN_ID

### 取得所有索引資料

SELECT 
  I.TABLE_OWNER, I.TABLE_NAME, I.INDEX_NAME, I.INDEX_TYPE,
  I.UNIQUENESS, C.COLUMN_POSITION, C.COLUMN_NAME, C.DESCEND
FROM 
  ALL_INDEXES I JOIN ALL_IND_COLUMNS C
ON 
  I.TABLE_OWNER = C.TABLE_OWNER AND
  I.INDEX_NAME = C.INDEX_NAME
WHERE
  C.TABLE_OWNER = 'DBAccount'
ORDER BY I.TABLE_NAME, I.INDEX_NAME, COLUMN_POSITION

### 取得主鍵值(Primary Key)欄位

SELECT 
  C.OWNER, C.TABLE_NAME, D.POSITION, D.COLUMN_NAME  
FROM 
  ALL_CONSTRAINTS C JOIN ALL_CONS_COLUMNS D
ON
  C.OWNER = D.OWNER AND
  C.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE
  C.CONSTRAINT_TYPE = 'P' AND C.OWNER = 'DBAccount'
ORDER BY C.TABLE_NAME, D.POSITION

參考自:
https://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name-ms-sql-server

###### tags: `SQL` `ms sql`