Ricerca in tutto il DB

[Luigi]

Ciao a tutti,
volevo sapere se esiste un modo per effettuare una ricerca in tutti i campi
di tutte le tabelle di un database.
Il mio scopo sarebbe di trovare tutti i campi (colonne) che abbiano una
certa estensione nel nome (ad es. _en per i campi in inglese), e questo per
tutte le tabelle del mio Db.
Potrei aprire tabella per tabella (attraverso l'EM), ma se le tabelle sono
qualche centinaio, come nel mio caso, il lavoro diventa lunghissimo.
Non ho però trovato un modo per effettuare ciò.
Qualcuno ha un'idea?
Grazie

[AlessandroD]

Post by Luigi
Ciao a tutti,
volevo sapere se esiste un modo per effettuare una ricerca in tutti i campi
di tutte le tabelle di un database.
Il mio scopo sarebbe di trovare tutti i campi (colonne) che abbiano una
certa estensione nel nome (ad es. _en per i campi in inglese), e questo per
tutte le tabelle del mio Db.
Potrei aprire tabella per tabella (attraverso l'EM), ma se le tabelle sono
qualche centinaio, come nel mio caso, il lavoro diventa lunghissimo.
Non ho però trovato un modo per effettuare ciò.
Qualcuno ha un'idea?

Tempo fa avevo avuto anche io la stessa necessità e quello che è uscito è
questo:

declare @nvcCampo sysname

set @nvcCampo= 'COD_UBI' /* Campo da cercare */
set @nvcCampo= '%' + @nvcCampo + '%'

select distinct
t.TABLE_NAME, c.COLUMN_NAME
from
INFORMATION_SCHEMA.COLUMNS c
inner join
INFORMATION_SCHEMA.TABLES t
on
c.TABLE_NAME= t.TABLE_NAME
where
t.TABLE_TYPE= 'BASE TABLE' and c.COLUMN_NAME like @nvcCampo
order by
t.TABLE_NAME

E mi pare che funzionava...
Ciao, Alessandro

[Lorenzo Benaglia]

Post by Luigi
Il mio scopo sarebbe di trovare tutti i campi (colonne) che abbiano
una certa estensione nel nome (ad es. _en per i campi in inglese), e
questo per tutte le tabelle del mio Db.
Potrei aprire tabella per tabella (attraverso l'EM), ma se le tabelle
sono qualche centinaio, come nel mio caso, il lavoro diventa
lunghissimo.

Ciao Luigi,

SQL Server 2000 offre la possibilità di leggere i metadati senza la
necessità di accedere alle tabelle di sistema ricorrendo alle Information
Schema Views.
Le Information Schema Views aderiscono allo standard ANSI SQL-92 e
permettono alle applicazioni di funzionare correttamente anche nel caso in
cui le tabelle di sistema subiscano notevoli modifiche (per esempio dovute
all'installazione di un service pack).

Nel tuo caso potresti ricorrere alla Information Schema View COLUMNS.
Nel seguente esempio recupero tutte le colonne di viste e tabelle che
teminano con il suffisso "ID":

USE Northwind
GO

SELECT
TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE N'%ID'
GO

/* Output:

TABLE_SCHEMA TABLE_NAME COLUMN_NAME
------------- -------------------- ------------
dbo sysconstraints colid
dbo sysconstraints constid
dbo sysconstraints id
dbo Employees EmployeeID
dbo Categories CategoryID
dbo Customers CustomerID
dbo dtproperties id
dbo dtproperties objectid
...
dbo EmployeeTerritories EmployeeID
dbo EmployeeTerritories TerritoryID
dbo New Order Details OrderID
dbo New Order Details ProductID

(46 row(s) affected)

*/

Se ti interessano solo le tabelle puoi utilizzare anche l'Information Schema
View TABLES:

SELECT
C.TABLE_SCHEMA
, C.TABLE_NAME
, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_CATALOG = T.TABLE_CATALOG AND
C.TABLE_SCHEMA = T.TABLE_SCHEMA AND
C.TABLE_NAME = T.TABLE_NAME
WHERE C.COLUMN_NAME LIKE N'%ID'
AND T.TABLE_TYPE = 'BASE TABLE'
GO

/* Output:

TABLE_SCHEMA TABLE_NAME COLUMN_NAME
------------- --------------------- ----------------
dbo Categories CategoryID
dbo CustomerCustomerDemo CustomerID
dbo CustomerCustomerDemo CustomerTypeID
dbo CustomerDemographics CustomerTypeID
dbo Customers CustomerID
dbo dtproperties id
dbo dtproperties objectid
dbo Employees EmployeeID
dbo EmployeeTerritories EmployeeID
dbo EmployeeTerritories TerritoryID
dbo New Order Details OrderID
dbo New Order Details ProductID
dbo Order Details OrderID
dbo Order Details ProductID
dbo Orders OrderID
dbo Orders CustomerID
dbo Orders EmployeeID
dbo Products ProductID
dbo Products SupplierID
dbo Products CategoryID
dbo Region RegionID
dbo Shippers ShipperID
dbo Suppliers SupplierID
dbo Territories TerritoryID
dbo Territories RegionID

(25 row(s) affected)

*/

Post by Luigi
Grazie

Prego.

Ciao!

[Luigi]

Grazie ad entrambi.
In effetti così facendo è molto più semplice e veloce.
Ho utilizzato il pezzo di codice:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%_en'
order by 2

C'è solo una cosa strana; oltre ai campi che terminano con _en (e quelli mi
vanno bene), mi esce anche un campo
Au_BasePriceHidden

che non termina con _en.
Molto strano eh. Comunque non è un problema, li filtro poi io a vista.

[Lorenzo Benaglia]

Post by Luigi
C'è solo una cosa strana; oltre ai campi che terminano con _en (e
quelli mi vanno bene), mi esce anche un campo
Au_BasePriceHidden
che non termina con _en.
Molto strano eh. Comunque non è un problema, li filtro poi io a vista.

Non è molto strano, in quanto il carattere underscore "_" è una wildcard che
rappresenta un singolo carattere alfanumerico.
La colonna "Au_BasePriceHidden" termina correttamente per "en".
Racchiudendo il carattere underscore tra parentesi quadre risolverai il
problema:

USE tempdb
GO

/* Definisco la tabella dbo.myTable */
CREATE TABLE dbo.myTable(
Fielden int,
Field_en int
)
GO

SELECT
TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE N'%_en'
GO

/* Output:

TABLE_SCHEMA TABLE_NAME COLUMN_NAME
-------------- ----------- -------------
dbo myTable Field_en
dbo myTable Fielden

(2 row(s) affected)

*/

SELECT
TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE N'%[_]en'
GO

/* Output:

TABLE_SCHEMA TABLE_NAME COLUMN_NAME
-------------- ----------- -------------
dbo myTable Field_en

(1 row(s) affected)

*/

/* Pulizia */
DROP TABLE dbo.myTable

Ciao!

[Luigi]

Eh già, alla fine gli dicevo di cercare
"tutti i caratteri"+"un qualsiasi carattere"+en
e quindi era per quello che mi ritornava più campi del normale.
Grazie Lorenzo!