Last active
January 9, 2021 03:51
-
-
Save lockworld/6c319f0758fd86ba2b95cb2c37a75744 to your computer and use it in GitHub Desktop.
Thse queies can be used to find information in tables, views, or object definitin
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Use this query to find the name of a database that has been renamed so you can change the name in all views, stored prcedures, functions, etc. | |
*/ | |
DECLARE @Search varchar(255) | |
SET @Search='%Old_DB_Name%' | |
SELECT DISTINCT | |
DB_NAME() as DBNAME, schema_name(o.schema_id) as SchName, o.name AS Object_Name,o.type_desc | |
FROM sys.sql_modules m | |
INNER JOIN sys.objects o ON m.object_id=o.object_id | |
WHERE m.definition Like '%'+@Search+'%' | |
ORDER BY 2,1 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Source: https://stackoverflow.com/a/14985918 | |
*/ | |
DECLARE @SearchStr nvarchar(100) = N'foo' -- The search string | |
DECLARE @dml nvarchar(max) = N'' | |
DECLARE @DbSearchResults TABLE | |
([tablename] nvarchar(100), | |
[ColumnName] nvarchar(100), | |
[Value] nvarchar(max)) | |
SELECT @dml += ' SELECT ''' + s.name + '.' + t.name + ''' AS [tablename], ''' + | |
c.name + ''' AS [ColumnName], CAST(' + QUOTENAME(c.name) + | |
' AS nvarchar(max)) AS [Value] FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + | |
' (NOLOCK) WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ' + '''%' + @SearchStr + '%''' | |
FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id | |
JOIN sys.columns c ON t.object_id = c.object_id | |
JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c .user_type_id = ty .user_type_id | |
WHERE t.is_ms_shipped = 0 AND ty.name NOT IN ('timestamp', 'image', 'sql_variant') | |
INSERT @DbSearchResults | |
EXEC sp_executesql @dml | |
SELECT * | |
FROM @DbSearchResults |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment