USE myDatabase GO SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%ColumnNameToFind%' ORDER BY schema_name, table_name;
15 February 2016
I'm doing a "black box" project, where I need to discover how a vendor app's database works. To that end, I've found this T-SQL useful in searching across the 140 tables for a column name, thanks to the tip from Pinal Dave's site,
11 February 2016
I'm working on a large database on the developer server and need a way to sift through and analyze the data. This is for a project where we're going to discard these tables and build a new system; in the meantime, I need to work with the copy of the old data. To that end, I created this T-SQL script to append all empty tables' names with '__EMPTY'.
declare @new_name varchar(max), @query varchar(max) IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp ;WITH TableRows AS ( SELECT SUM(row_count) AS [RowCount], OBJECT_NAME(OBJECT_ID) AS TableName FROM sys.dm_db_partition_stats WHERE (index_id = 0 OR index_id = 1) and (LEFT(OBJECT_NAME(OBJECT_ID), 3) <> 'sys') GROUP BY OBJECT_ID ) SELECT * into #temp FROM TableRows WHERE [RowCount] = 0 order by TableName DECLARE db_cursor CURSOR FOR SELECT tablename FROM #temp OPEN db_cursor declare @name varchar(max) FETCH NEXT FROM db_cursor INTO @name WHILE (@@FETCH_STATUS = 0) BEGIN set @new_name = @name + '__EMPTY' set @query = 'exec sp_rename ''' + @name + ''',''' + @new_name + '''' exec(@query) FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor deallocate db_cursor IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #tempI used the technique described in this Stack Overflow post to find the empty tables.
05 February 2016
04 February 2016
I needed to add some references in an Access 2013 code module. Going to Tools, I found the References item greyed out. Research led to this article with a registry key to enable References:
[HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Access\Security] "AccessVBOM"=dword:00000001Note that I am an admin on this machine, running Office 2013 (hence the "15.0") and need VBA in Access. You'll need to modify the above to your needs.