Je suis perdu dans une grande base de données et je ne suis pas en mesure de trouver d'où proviennent les données que je reçois. Je me demandais s'il était possible avec SQL Server 2005 de rechercher une chaîne dans toutes les tables, lignes et colonnes d'une base de données?
Quelqu'un a-t-il une idée si c'est possible et comment?
Merci!
Ce code doit le faire en SQL 2005, mais quelques bémols:
Il suppose que tous les objets sont la propriété de dbo. Si ce n'est pas le cas, vous aurez besoin de l'ajuster.
Il est RIDICULEMENT lent. Je l'ai testé sur une petite base de données que j'ai avec seulement une poignée de tables et il a fallu plusieurs minutes pour terminer. Si votre base de données est tellement grande que vous ne pouvez pas le comprendre, alors ce sera probablement inutilisable de toute façon.
J'ai écrit ce improvisée. Je n'ai pas mis dans toute erreur de manipulation et il pourrait y avoir quelques autres médiocrité surtout depuis que je ne pas utiliser des curseurs souvent. Par exemple, je pense qu'il y a une manière d'actualiser les colonnes du curseur, au lieu de fermer/désallocation/recréer à chaque fois.
Si vous ne pouvez pas comprendre la base de données ou vous ne savez pas où des choses, alors vous devriez probablement trouver quelqu'un qui ne. Même si vous pouvez trouver l'endroit où les données, il peut être dupliqué quelque part ou il y a peut être d'autres aspects de la base de données que vous ne comprenez pas. Si personne dans votre entreprise comprend la base de données, alors vous êtes dans un assez grand désordre.
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'Test'
DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
EXECUTE(@sql_string)
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
Je vous suggère de vous trouver vous-même une 3ème partie pour cela l'outil comme ApexSQL de Recherche (il y en a probablement d'autres aussi, mais je l'utiliser parce que c'est gratuit).
Si vous voulez vraiment aller le SQL façon, vous pouvez essayer d'utiliser la procédure stockée créé par Sorna Kumar Muthuraj – copié le code est ci-dessous. Juste exécuter cette procédure stockée pour toutes les tables de votre schéma (facile avec la dynamique SQL)
CREATE PROCEDURE SearchTables
@Tablenames VARCHAR(500)
,@SearchStr NVARCHAR(60)
,@GenerateSQLOnly Bit = 0
AS
/*
Parameters and usage
@Tablenames -- Provide a single table name or multiple table name with comma seperated.
If left blank , it will check for all the tables in the database
@SearchStr -- Provide the search string. Use the '%' to coin the search.
EX : X%--- will give data staring with X
%X--- will give data ending with X
%X%--- will give data containig X
@GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.
By default it is 0 and it will search.
Samples :
1. To search data in a table
EXEC SearchTables @Tablenames = 'T1'
,@SearchStr = '%TEST%'
The above sample searches in table T1 with string containing TEST.
2. To search in a multiple table
EXEC SearchTables @Tablenames = 'T2'
,@SearchStr = '%TEST%'
The above sample searches in tables T1 & T2 with string containing TEST.
3. To search in a all table
EXEC SearchTables @Tablenames = '%'
,@SearchStr = '%TEST%'
The above sample searches in all table with string containing TEST.
4. Generate the SQL for the Select statements
EXEC SearchTables @Tablenames = 'T1'
,@SearchStr = '%TEST%'
,@GenerateSQLOnly = 1
*/
SET NOCOUNT ON
DECLARE @CheckTableNames Table
(
Tablename sysname
)
DECLARE @SQLTbl TABLE
(
Tablename SYSNAME
,WHEREClause VARCHAR(MAX)
,SQLStatement VARCHAR(MAX)
,Execstatus BIT
)
DECLARE @sql VARCHAR(MAX)
DECLARE @tmpTblname sysname
IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
BEGIN
INSERT INTO @CheckTableNames
SELECT Name
FROM sys.tables
END
ELSE
BEGIN
SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + ''''
INSERT INTO @CheckTableNames
EXEC(@sql)
END
INSERT INTO @SQLTbl
( Tablename,WHEREClause)
SELECT SCh.name + '.' + ST.NAME,
(
SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10)
FROM SYS.columns SC
JOIN SYS.types STy
ON STy.system_type_id = SC.system_type_id
AND STy.user_type_id =SC.user_type_id
WHERE STY.name in ('varchar','char','nvarchar','nchar')
AND SC.object_id = ST.object_id
ORDER BY SC.name
FOR XML PATH('')
)
FROM SYS.tables ST
JOIN @CheckTableNames chktbls
ON chktbls.Tablename = ST.name
JOIN SYS.schemas SCh
ON ST.schema_id = SCh.schema_id
WHERE ST.name <> 'SearchTMP'
GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ;
UPDATE @SQLTbl
SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)
DELETE FROM @SQLTbl
WHERE WHEREClause IS NULL
WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
BEGIN
SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement
FROM @SQLTbl
WHERE ISNULL(Execstatus ,0) = 0
IF @GenerateSQLOnly = 0
BEGIN
IF OBJECT_ID('SearchTMP','U') IS NOT NULL
DROP TABLE SearchTMP
EXEC (@SQL)
IF EXISTS(SELECT 1 FROM SearchTMP)
BEGIN
SELECT Tablename=@tmpTblname,* FROM SearchTMP
END
END
ELSE
BEGIN
PRINT REPLICATE('-',100)
PRINT @tmpTblname
PRINT REPLICATE('-',100)
PRINT replace(@sql,'INTO SearchTMP','')
END
UPDATE @SQLTbl
SET Execstatus = 1
WHERE Tablename = @tmpTblname
END
SET NOCOUNT OFF
go
Bien que les solutions présentées avant sont valables et de travail, je humblement vous proposons un code plus propre, plus élégante, et avec une meilleure performance, à moins que je le vois.
Tout d'abord, on peut se demander: Pourquoi serait-on toujours besoin d'un extrait de code à l'échelle mondiale et à l'aveugle rechercher une chaîne de caractères? Hey, ils ont déjà inventé fulltext, ne savez-vous pas?
Ma réponse: principalement mon travail est à l'intégration des systèmes de projets, et de découvrir où les données sont écrites est important à chaque fois que j'apprends un nouveau et undocummented base de données, ce qui arrive rarement.
Aussi, le code que j'ai actuellement est une version allégée d'un plus puissant et dangereux script qui cherche et REMPLACE le texte dans la base de données.
CREATE TABLE #result(
id INT IDENTITY, -- just for register seek order
tblName VARCHAR(255),
colName VARCHAR(255),
qtRows INT
)
go
DECLARE @toLookFor VARCHAR(255)
SET @toLookFor = '[input your search criteria here]'
DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
'[' + usr.name + '].[' + tbl.name + ']' AS tblName,
'[' + col.name + ']' AS colName,
LOWER(typ.name) AS typName
FROM
sysobjects tbl
INNER JOIN(
syscolumns col
INNER JOIN systypes typ
ON typ.xtype = col.xtype
)
ON col.id = tbl.id
--
LEFT OUTER JOIN sysusers usr
ON usr.uid = tbl.uid
WHERE tbl.xtype = 'U'
AND LOWER(typ.name) IN(
'char', 'nchar',
'varchar', 'nvarchar',
'text', 'ntext'
)
ORDER BY tbl.name, col.colorder
--
DECLARE @tblName VARCHAR(255)
DECLARE @colName VARCHAR(255)
DECLARE @typName VARCHAR(255)
--
DECLARE @sql NVARCHAR(4000)
DECLARE @crlf CHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
OPEN cCursor
FETCH cCursor
INTO @tblName, @colName, @typName
WHILE @@fetch_status = 0
BEGIN
IF @typName IN('text', 'ntext')
BEGIN
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
SET @sql = @sql + 'FROM ' + @tblName + @crlf
SET @sql = @sql + 'WHERE PATINDEX(''%'' + @toLookFor + ''%'', ' + @colName + ') > 0' + @crlf
END
ELSE
BEGIN
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
SET @sql = @sql + 'FROM ' + @tblName + @crlf
SET @sql = @sql + 'WHERE ' + @colName + ' LIKE ''%'' + @toLookFor + ''%''' + @crlf
END
EXECUTE sp_executesql
@sql,
N'@tblName varchar(255), @colName varchar(255), @toLookFor varchar(255)',
@tblName, @colName, @toLookFor
FETCH cCursor
INTO @tblName, @colName, @typName
END
SELECT *
FROM #result
WHERE qtRows > 0
ORDER BY id
GO
DROP TABLE #result
go
La SSMS PACK des Outils d'complément (Add-on) pour Microsoft SQL Server Management Studio et Microsoft SQL Server Management Studio Express, faire exactement ce dont vous avez besoin. Sur la plus grande base de données il faut un certain temps à la recherche, mais qui doit être prévu. Il comprend également une tonne de fonctionnalités intéressantes qui devraient être inclus avec SQL Server Management Studio en premier lieu. Lui donner un essai www.ssmstoolspack.com/
Vous avez besoin d'avoir le SP2 de SQL Server Management Studio est installé pour exécuter les outils.
Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.
