Tuesday, 30 July 2013

SQL : Data Search Script - Version 5

version 5 :        1) copes with schemas other than dbo!
                        2) restrict to tables only (was doing views!)
                        3) nolock table hint added
                        4) uniqueidentifier added to data types searched
                        5) relays progress to window instantly using raiserror
(http://sqlsolace.blogspot.co.uk/2008/09/sql-messages-window-get-messages.html)




/*
DATA SEARCH SCRIPT

version 5 :  
   1) copes with schemas other than dbo!
   2) restrict to tables only (was doing views!)
   3) nolock table hint added
   4) uniqueidentifier added to data types searched
   5) relays progress to window instantly using raiserror (http://sqlsolace.blogspot.co.uk/2008/09/sql-messages-window-get-messages.html)

YOU MUST RUN THIS IN GRID VIEW!!! 
*/

declare @columncount int
declare @searchdata varchar(2550)
-- set string to search for here
set @searchdata = 'doering'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','char', 'varchar', 'nvchar', 'nvarchar')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock)  where type = 'u')
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' + 
  table_schema + '].[' + table_name + 
  '] with (nolock) where [' + 
  column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
  ' begin ' + char(10) +
  '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
  '  raiserror( ''' + table_schema + '.' + table_name  + '/' +column_name  + ''', 10, 1) WITH NOWAIT' + char(10) + 
  ' end ' + char(10) + 
  '' + char(10) +
  ' raiserror( ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%%' +
  ''', 10, 1) WITH NOWAIT' + char(10)
 from  information_schema.columns with (nolock)
 where data_type in ('ntext','char', 'varchar', 'nvchar', 'nvarchar')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
union
select 'print @resultslist' + char(10) 

Thursday, 4 July 2013

SQL 2012 - sp_dboption is no more....

Ultra confident a set of trusted scripts would work on SQL 2012, I came across this today, sp_dboption has gone from SQL 2012.

The solution is to use the relevent ALTER DATABASE syntax.

Old : EXEC sp_dboption 'Adventureworks', 'single user', 'TRUE';
New : ALTER DATABASE Adventureworks SET SINGLE_USER

Old : EXEC sp_dboption 'Adventureworks', 'single user', 'FALSE';
New : ALTER DATABASE Adventureworks SET MULTI_USER

This post from Jacob Sebastian details the rest ...
Replacement for system stored procedure sp_dboption in SQL Server 2012 (Denali)


Wednesday, 22 May 2013

Link : Move Database Data Without Taking the Database Offline

This article shows how you can move database data to a new .MDF data file via the ALTER DATABASE and DBCC SHRINKFILE commands.

Pros
  1. Can be done online 

Cons
  1. Speed 
  2. Cannot move system objects 
  3. Index Fragmentation may be caused

Friday, 26 April 2013

Link :32Bit SQL on a 64 Bit Box

I've seen this on a number of boxes now.
32bit SQL in a 64bit environment.

{screams silently}

Thomas LaRock has a script to test for this -

HOW TO: Determine If You Are Running A 32-bit Version Of SQL Server On A 64-bit O/S

Wednesday, 3 April 2013

MCSE : Data Platform SQL 2012

Over the past 3 weeks I have taken (and passed) the following three SQL Server 2012 upgrade exams -
The first 2 exams made me an MCSA, completing the third made me an MCSE for Data platform. A little confusing as I was already an MCSE some years ago. To clarify...

The old world MCSE (I did mine on Windows 2000) stood for Microsoft Certified Systems Engineer.
The new MCSE is an acronym for Microsoft Certified Solutions Expert.

All the new professional exams are MCSEs with the exact titles differing as per the product specialism.

Link : New MCSE Certifications

Monday, 1 April 2013

Link : Relational Databases Aren't Dead.

I like this link.

I like it because academic developers whom are keen on CV polishing are convinced the RDBMS is old news. If it's not an ORM model or even better a NoSQL implementation like Cassandra or MongoDB then it's old hat.

Yes, they can scale immensely which is fantastic but they are not suitable everywhere, Those implementations don't lend themselves to structure, to referential integrity and the beauty of having one master version of a data set.

I'll let the title speak for itself ...

Relational Databases Aren't Dead. Heck, They're Not Even Sleeping

Wednesday, 27 March 2013

Formatting FAT32 Volumes larger than 32 GB

For an exercise in (ahem) modding a games console, I needed to format an external USB drive to FAT32.
On doing so, I found I couldn't do so...

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.


C:\Users\RMD>format g: /q /fs:fat32

The type of the file system is NTFS.
The new file system is FAT32.

WARNING, ALL DATA ON NON-REMOVABLE DISK
DRIVE G: WILL BE LOST!
Proceed with Format (Y/N)? y
QuickFormatting 238472M
The volume is too big for FAT32.



The solution I used was to find a tool that would perform the format.
Fat32Format by Ridgecrop Consultants is as good as any, i.e. it is fast and free.

C:\Users\RMD>fat32format g:

Warning ALL data on drive 'g' will be lost irretrievably, are you sure
(y/n) :y
Size : 250GB 488392002 sectors
512 Bytes Per Sector, Cluster size 32768 bytes
Volume ID is 1ee6:304a
32 Reserved Sectors, 59604 Sectors per FAT, 2 fats
7629261 Total clusters
7629260 Free Clusters
Formatting drive g:...
Clearing out 119304 sectors for Reserved sectors, fats and root cluster...
Wrote 61083648 bytes in 2.33 seconds, 25.05 Megabytes/sec
Initialising reserved sectors and FATs...
Done

C:\Users\RMD>