あるユーザのテーブルの中で、特定の列名を持つテーブルを検索したい時の方法。
PostgreSQL編はこちら↓
www.k-hitorigoto.online
Oracleの場合
USER_TAB_COLUMNSを参照。
https://docs.oracle.com/cd/E49329_01/server.121/b71292/refrn20277.htm#I1020277
SELECT TABLE_NAME ,COLUMN_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='DEPTNO' ORDER BY TABLE_NAME ,COLUMN_NAME ;
実行結果。SCOTTスキーマの場合。
| TABLE_NAME | COLUMN_NAME |
| DEPT | DEPTNO |
| EMP | DEPTNO |
SQLServerの場合
INFORMATION_SCHEMAを参照。
COLUMNS (Transact-SQL) - SQL Server | Microsoft Learn
SQLServerの場合、ユーザ名≠スキーマ名なので、スキーマ名も取得した。
SELECT TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'NAME' ORDER BY TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME ;
実行結果。AdventureWorksの場合。
| TABLE_SCHEMA | table_name | column_name |
| HumanResources | Department | Name |
| HumanResources | Shift | Name |
| Person | AddressType | Name |
| Person | ContactType | Name |
| Person | CountryRegion | Name |
| Person | PhoneNumberType | Name |
| Person | StateProvince | Name |
| Production | Culture | Name |
| Production | Location | Name |
| Production | Product | Name |
| Production | ProductCategory | Name |
| Production | ProductModel | Name |
| Production | ProductSubcategory | Name |
| Production | ScrapReason | Name |
| Production | UnitMeasure | Name |
| Production | vProductAndDescription | Name |
| Production | vProductModelCatalogDescription | Name |
| Production | vProductModelInstructions | Name |
| Purchasing | ShipMethod | Name |
| Purchasing | Vendor | Name |
| Purchasing | vVendorWithAddresses | Name |
| Purchasing | vVendorWithContacts | Name |
| Sales | Currency | Name |
| Sales | SalesReason | Name |
| Sales | SalesTaxRate | Name |
| Sales | SalesTerritory | Name |
| Sales | Store | Name |
| Sales | vStoreWithAddresses | Name |
| Sales | vStoreWithContacts | Name |
| Sales | vStoreWithDemographics | Name |
