CREATE PROCEDURE SearchTables
@Tablenames VARCHAR(500)
,@SearchStr NVARCHAR(60)
,@GenerateSQLOnly Bit = 0
AS
/*
https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58
Sorna Kumar Muthuraj
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
select isnull(db1.table_name, db2.table_name) as [table],
isnull(db1.column_name, db2.column_name) as [column],
db1.column_name as database1,
db2.column_name as database2
from
(select schema_name(tab.schema_id) + '.' + tab.name as table_name,
col.name as column_name
from crouseorg_live.sys.tables as tab
inner join crouseorg_live.sys.columns as col
on tab.object_id = col.object_id) db1
full outer join
(select schema_name(tab.schema_id) + '.' + tab.name as table_name,
col.name as column_name
from crouseorg_live_dev.sys.tables as tab
inner join crouseorg_live_dev.sys.columns as col
on tab.object_id = col.object_id) db2
on db1.table_name = db2.table_name
and db1.column_name = db2.column_name
where (db1.column_name is null or db2.column_name is null)
order by 1, 2, 3
https://dataedo.com/kb/query/sql-server/compare-tables-and-columns-in-two-databases