15 February 2016

Find a column name across all tables on SQL Server

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,
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;

11 February 2016

Rename all empty SQL Server tables

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 #temp
I used the technique described in this Stack Overflow post to find the empty tables.

05 February 2016

SamsaraJS: Famo.us fork juices mobile Web UIs

The library provides native-like app experiences and takes on the mobile Web's performance issues: SamsaraJS: Famo.us fork juices mobile Web UIs

04 February 2016

References greyed out in VBA window

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:00000001
Note 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.