I’m a big fan of Redgate SQL Compare but it’s been good to see the arrival of a few free alternatives. Life previous to these tools really does seem like the stone age now eliminating those "oh $h**, I forgot about that!" moments. I’d always go for Redgate every time but, if you don’t have [...]
Following on from yesterdays blog post, a MySQL clone of sp_MsForEachTable, here’s an attempt at a clone of sp_spaceused. There’s a few issues to be aware of involving the storage engine in use. For example the row count is accurate for MyISAM while with InnoDb it seems to be just an estimate. This estimate can [...]
Many SQL Server DBAs and Developers get a lot of use out of the undocumented sp_MsForEachTable system stored procedure. Here’s an attempt at creating a functional version for MySQL. The procedure makes use of prepared statements, which do have some limitations, so some uses may not translate across. This is far from production ready so [...]
Here’s a Powershell script that can be used to manage index fragmentation in SQL Server databases. The strategy I’ve used in the script is based on a recommendation from Pinal Dave (blog | twitter) in his article Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script. Just set the $sqlserver and $database variables [...]
Just a quick post showing how to add a Postgres database server as a Linked Server in Microsoft SQL Server.
Install the psqlODBC driver for Windows.
Control Panel > Administrative Tools > Data Sources (ODBC).
Click the "System DSN" tab and click the Add button.
Choose "PostreSQL ANSI" and click Finish.
Configure the data source by [...]
I’ve blogged before about documenting databases. I’m very much a fan of extracting documentation from systems themselves so it’s as up-to-date as it can be. That’s probably why I’m such a big fan of Powershell a tool that excels at this task. This week I was thinking about how to get at the comments often [...]
If you ever encounter this SQL Server error when selecting from a view then somebody has probably dropped columns from the base table. Here’s a quick run through of the problem.
?View Code TSQLCREATE TABLE dbo.Contact
(
Id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DOB DATETIME NOT NULL,
Phone VARCHAR(30) NULL,
Email VARCHAR(200) NULL,
Mobile [...]
Today I received the following SQL Server error which I had never encountered before.
Msg 4429, Level 16, State 1, Line 1 View or function ‘Table_1′ contains a self-reference. Views or functions cannot reference themselves directly or indirectly. Msg 4413, Level 16, State 1, Line [...]
I’m a little late blogging about this but I have these goals in my mind for this year.
Get more organised. I have days where I’m super productive, and others where I feel like a dog chasing his own tail. I’ve seen a few people recommend Getting things done by David Allen. So I’ve bought a [...]
Brent Ozar is the sexiest DBA when I’m not in my special pants!
