Useful SQL Server scripts: 2009

Wednesday, October 28, 2009

Getting started with SQL Azure

Getting started with SQL Azure

http://www.sqlservercentral.com/articles/SQL+Azure/68333/

Insert into a table with identity column

this is an easy way to insert into a table with default valus that has a identoty column.

this sample will insert 500 records.

WHILE SCOPE_IDENTITY()<500 OR SCOPE_IDENTITY() IS NULL INSERT INTO test1 default VALUES

Monday, September 21, 2009

Collect DB sizes across all Servers

drop table #tmp
create table #tmp (name nvarchar(100),fileid int,[filename]nvarchar(100),filegroup nvarchar(100),size nvarchar(100),maxsize nvarchar(100),growth nvarchar(100),usage nvarchar(100),)

EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
insert into #tmp
exec sp_helpfile
--exec sp_spaceused
end
'
go

select * From #tmp

---------------------------------------------
--ANOTHER METHOD
---------------------------------------------

drop table #tmp
create table #tmp (name nvarchar(100),fileid int,[filename]nvarchar(100),filegroup nvarchar(100),size nvarchar(100),maxsize nvarchar(100),growth nvarchar(100),usage nvarchar(100),db nvarchar(100))
drop table #tmpfinal
create table #tmpfinal (name nvarchar(100),fileid int,[filename]nvarchar(100),filegroup nvarchar(100),size nvarchar(100),maxsize nvarchar(100),growth nvarchar(100),usage nvarchar(100),db nvarchar(100))

EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
insert into #tmp (name,fileid,filename,filegroup,size,maxsize,growth,usage)
exec sp_helpfile
--exec sp_spaceused
insert into #tmpfinal (name,fileid,filename,filegroup,size,maxsize,growth,usage,db)
select name,fileid,filename,filegroup,size,maxsize,growth,usage,''?'' from #tmp
truncate table #tmp
end
'

select * From #tmpfinal where filename like '%ftrow_ft_modules.ndf%'

Find a string in Entire Server's Stored Procedures

EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
select ''?'' as DB, o.name, c.[text] --,*
from dbo.sysobjects o inner join dbo.syscomments c on o.[id] = c.[id] where c.[text] like ''%MemberSite_Current%''
end
'
go

Thursday, September 17, 2009

Drop User, Drop Owned Schema, Create User, Add RoleMember

master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''IF EXISTS (SELECT * FROM sys.schemas WHERE name = N''''SPARKSTAGE\developers'''')''
print ''DROP SCHEMA [SPARKSTAGE\developers]''
print ''go''
print ''IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''''SPARKSTAGE\developers'''')''
print ''DROP USER [SPARKSTAGE\developers]''
print ''go''
print ''CREATE USER [SPARKSTAGE\developers] FOR LOGIN [SPARKSTAGE\developers]''
print ''go''
print ''EXEC sp_addrolemember N''''db_datareader'''', N''''SPARKSTAGE\developers''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_datawriter'''', N''''SPARKSTAGE\developers''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_executor'''', N''''SPARKSTAGE\developers''''''
print ''go''
end
'
go

Friday, September 11, 2009

Writting into Win Application log from T-SQL

declare @msg varchar(1000)
set @msg = 'LADBREPORT.mnSubscription.dbo.MemberTran table is missing records'
RAISERROR (@Msg, 16, 1) WITH LOG --> this is Error
exec xp_logevent 60000,@Msg, WARNING --> this is Warning

Thursday, September 10, 2009

GRANT EXECUTE ON StoredProcedures TO Account

master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)''
print ''set @user = ''''db_executor''''''
print ''DECLARE Cur_name CURSOR FOR ''
print ''select specific_name From information_Schema.routines''
print ''OPEN Cur_name''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''WHILE @@FETCH_STATUS = 0''
print ''BEGIN''
print ''set @string =''''GRANT EXECUTE ON [dbo].[''''+@name+''''] TO [''''+@user+'''']''''''
print ''print @string''
print ''exec sp_executesql @string''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''END''
print ''CLOSE Cur_name''
print ''DEALLOCATE Cur_name''
print ''go''
end
'
go

Tuesday, September 08, 2009

Xml data type is not supported in distributed queries

we can use OPENQUERY to call the table that has XML data type.

decalre @string nvarchar(4000), @StartDate datetime, @EndDate datetime
set @StartDate = dateadd(dd,-30,getutcdate())
set @EndDate = dateadd(dd,0,getutcdate())

set @string = '
select * from
OPENQUERY([lasqlstage101.sparkstage.com],''
SELECT
OrderID,
Cast(OrderAttributeXML as Varchar(1000)) as OrderAttributeXML,
Insertdate
FROM
[eporder].[dbo].[OrderAttributeValue]
WHERE
insertdate >= '''''+convert(nvarchar(10),@StartDate,101)+' '+convert(nvarchar(10),@StartDate,108)+''''' and insertdate < '''''+convert(nvarchar(10),@EndDate,101)+' '+convert(nvarchar(10),@EndDate,108)+''''' ''
)
'

exec sp_executesql @string

Connecting to SQL Server Named Instances

In order to connect to a named instance of SQL server :

1-make sure remote access is open
2-RCP/IP of named instnace has a static SQL port configured
3-Open Firewall ports for Static SQL Port of named instance
4-Open Firewall ports for 1434 for SQL Server Browser

Wednesday, August 26, 2009

Database Mirroring Across Domains, Using Certificates.....

Monday, August 24, 2009

Find a Table withing SQL server instance with LIKE

EXEC master..sp_MSForEachDB '

if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
select * from information_schema.tables where table_name like ''%billing_%''
end

'
go

Tuesday, August 18, 2009

Script to auto generate a security report for your SQL Server instance

Monday, July 13, 2009

RESEED IDENTITY Column

DBCC CHECKIDENT (orders, NORESEED)
DBCC CHECKIDENT (orders, RESEED, 999)

Friday, July 10, 2009

Automated Trace File Collection

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

set @DateTime = '2009-07-10 21:00:00.000'
set @DateTime = dateadd(mi,2,GETDATE())
--select @DateTime
set @maxfilesize = 20

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
declare @filename nvarchar(100)
set @filename = N'C:\SQL_PROFILER\SQL01-mnmember1'
exec @rc = sp_trace_create @TraceID output, 0, @filename, @maxfilesize, @Datetime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 7
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - de04e276-c5f5-4a14-a12c-d8f42f0c5a8b'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

/*
SELECT * FROM :: fn_trace_getinfo(default)

-- Set the trace status to start
exec sp_trace_setstatus 2, 1
-- stops the trace
exec sp_trace_setstatus 2, 0
-- closes the trace and deletes its definition from the server
WAITFOR DELAY '00:00:30'
exec sp_trace_setstatus 2, 2

*/

Thursday, June 25, 2009

A simple Cursor sample

declare @mid int , @comid int
declare tmp_cur cursor for
select distinct memberid, communityid from _cleanup_dups order by memberid

open tmp_cur
Fetch next from tmp_cur into @mid, @comid

while @@fetch_status = 0
begin
print @mid
print @comid
Fetch next from tmp_cur into @mid, @comid
print '---'
end

close tmp_cur
deallocate tmp_cur

Wednesday, June 24, 2009

Sending scheduled SQL query results via HTML e-mail using SSIS

Sending email from SQL using sp_send_dbmail

declare @sub nvarchar(1000)
set @sub = 'Engage Daily Import Counts for: '+Convert(nvarchar(100),getdate(),101)

exec msdb.dbo.sp_send_dbmail
@Subject=@sub,
@query = '
set nocount on;
--select "Engage Import Results for: "+Convert(nvarchar(100),getdate(),101);
select count(*) as Users from PstgreSQL.dbo.users;
print "
";
select COUNT(*) as PostalCode from PstgreSQL.dbo.PostalCode;
print "
";
select COUNT(*) as Users_stage from PstgreSQL.dbo.Users_stage;
print "
";
select COUNT(*) as PostalCode_stage from PstgreSQL.dbo.PostalCode_stage;
',
@recipients = 'palexander@spark.net',
@body_format = 'HTML'

--select 'Import Results for: '+Convert(nvarchar(100),getdate(),101);

Friday, June 19, 2009

Automating Performance Monitor Statistics Collection for SQL Server and Windows

Tuesday, June 16, 2009

Saving and Retrieving File Using FileStream SQL Server 2008

Tuesday, June 09, 2009

Find a Table or view within entire SQL server

EXEC master..sp_MSForEachDB '
IF EXISTS
(
SELECT 1
FROM [?].INFORMATION_SCHEMA.tables
WHERE table_NAME = ''vw_fctmemberdetail_ca''
)
BEGIN
SELECT ''[?]'';

SELECT ''['' +table_CATALOG + '']'', ''['' + table_NAME + '']''
FROM [?].INFORMATION_SCHEMA.tables
WHERE table_NAME = ''vw_fctmemberdetail_ca'';
END';

Friday, May 29, 2009

Rob's SQL Server Blog

Rob's SQL Server Blog

Helpful Blog

Performance Dashboard Reports for SQL Server 2008

Thursday, May 28, 2009

Reindex Tables

--sp_helpindex 'jm_wo_transaction'
declare @tblname nvarchar(100),@sqlstring nvarchar(2000)
set @tblname = 'memberflat'

IF OBJECT_ID('tempdb..#tmpindexes') IS NOT NULL
drop table #tmpindexes

IF OBJECT_ID('tempdb..#tmptableindexes') IS NOT NULL
drop table #tmptableindexes

create table #tmptableindexes
(index_name nvarchar(200), index_description nvarchar(1000), index_keys nvarchar(1000), table_name nvarchar(100))
create table #tmpindexes
(index_name nvarchar(200), index_description nvarchar(1000), index_keys nvarchar(1000))

--select * from information_schema.tables where table_type = 'BASE TABLE'
--DECLARE tmptable_name CURSOR FOR select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'dbo'
DECLARE tmptable_name CURSOR FOR select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'dbo' and table_name=@tblname
OPEN tmptable_name

FETCH NEXT FROM tmptable_name INTO @tblname

WHILE @@FETCH_STATUS = 0
BEGIN
print 'print '''+@tblname+''''
print 'go'
set @sqlstring = 'DBCC DBREINDEX (['+@tblname+'],'''',90)'
print @sqlstring
--exec sp_executesql @sqlstring
print 'go'
FETCH NEXT FROM tmptable_name INTO @tblname
end

CLOSE tmptable_name
DEALLOCATE tmptable_name

Tuesday, April 28, 2009

Move tempdb mdf and ldf files to new drive

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'D:\data\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'D:\data\templog.ldf')
go

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 61440KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 61440KB )
GO

helpful article:
How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

Thursday, April 09, 2009

Rename MSSQL Server name

select @@servername
go
exec sp_dropserver 'CLSQL106'
go
sp_addserver 'CLSQL64', 'local'
go

Restart the server

Wednesday, April 08, 2009

Database level permissions for SQL Server 2005 and 2008

Database Permission Review

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

Monday, April 06, 2009

XML File Sample

<?xml version="1.0"?> <strings> <prompts> <form id="1"> <string name="firstname">First Name</string> <string name="lastname">Last Name</string> <string name="email">Email Address</string> <string name="gender">Gender</string> <string name="birthdate">Birth Date</string> <string name="looking_for">What type of relationship are you looking for?</string> <string name="accept_legal">Membership Agreement</string> <string name="optin_alerts">Email Alerts</string> </form> <form id="2"> <string name="body_type">Body Type</string> <string name="height">Height</string> <string name="eyes">Eye Color</string> <string name="hair">Hair Color</string> <string name="language">Languages Spoken</string> <string name="originally_from">Originally from</string> <string name="occupation">Occupation</string> <string name="education">Education</string> <string name="marital_status">Relationship Status</string> <string name="about_children">About Kids</string> <string name="children">Number of Children</string> <string name="children_at_home">Number of Children at Home</string> <string name="religion">Religion</string> <string name="smoke">Do you smoke?</string> <string name="drink">Do you drink?</string> <string name="ethnicity">Ethnicity</string> </form> <form id="3">    <string name="music">Which of these are your favorite type of music?</string> <string name="movie">Which of these are your favorite type of movie?</string>  <string name="outdoors">Which outdoor activities are you most interested in?</string> <string name="indoors">Which of these activities do you enjoy the most?</string>   <string name="punctual">When going somewhere...</string>  <string name="trendy">Are you a trendy person?</string>  <string name="food">Favorite types of food</string> <string name="political">What is your political leaning?</string> <string name="greeting">Introduce yourself and describe the type of person you'd like to meet.</string> <string name="first_date">What would you like to do on a first date?</string> <string name="self_description">Self-description</string> <string name="favorite_musicians">Favorite bands and musicians</string> <string name="favorite_movies">Favorite movies and actors</string> <string name="favorite_tv">Favorite TV shows</string> <string name="great_trip">My idea of a great trip</string> <string name="favorite_restaurants">Favorite restaurants</string> <string name="schools_attended">Schools attended</string> </form> <form id="4"> <string name="Absolute">IMPORTANT:</string> <string name="birthdate">Age</string> <string name="distance">Location</string> <string name="height">Height</string> <string name="Preferences">IMPORTANT:</string> <string name="body_type">Body Type</string> <string name="education">Education</string> <string name="marital_status">Relationship Status</string> <string name="religion">Religion</string> <string name="ethnicity">Ethnicity</string> <string name="smoke">Smoking</string> <string name="drink">Drinking</string> <string name="auto_email">Ideal Match Emails</string> </form> <form id="5"> <string name="greeting">Who am I and what am I looking for?</string> <string name="first_date">What would I like to do on a first date?</string> <string name="whyknowme">Why you should get to know me:</string> <string name="past_relationships">What good things have past relationships taught me?</string> <string name="lifeimportant">Some of the most important things in my life are:</string> <string name="perfectday">How would I describe my perfect day?</string> <string name="anythingelse">A few more things I would like to add:</string> </form> <form id="6"> <string name="qow">Question of the Week</string> </form> </prompts> <sub-prompts> <string name="email">You must enter a valid, working email address.</string> <string name="looking_for"/> </sub-prompts> <enum name="body_type"> <field value="8"></field> <field value="0">Slender</field> <field value="1">Washboard</field> <field value="2">Athletic</field> <field value="3">Average</field> <field value="4">I should maybe lose a few</field> <field value="5">Large but muscular</field> <field value="6">Full-figured</field> <field value="7">I'll tell you later</field> </enum>  <enum name="about_children"> <field value="4"></field> <field value="0">I don't have kids but I would like to</field> <field value="1">I have kids and I would like more</field> <field value="2">I don't have kids and I wouldn't like to have any</field> <field value="3">I have kids and I wouldn't like more</field> </enum> <enum name="children" show_values="true"> <field value="0">0</field> <field value="1">1</field> <field value="2">2</field> <field value="3">3</field> <field value="4">4</field> <field value="5">5+</field> </enum> <enum name="church_activity"> <field value="4"></field> <field value="0">on special occasions</field> <field value="1">several times a year</field> <field value="2">once or twice a month</field> <field value="3">every week</field> </enum> <enum name="church_importance"> <field value="4"></field> <field value="0">It is very important</field> <field value="1">It is somewhat important</field> <field value="2">It is starting to be important</field> <field value="3">I'm honestly not sure</field> </enum> <enum name="distance"> <field value="50">within 50 miles</field> <field value="100">within 100 miles</field> <field value="300">within 300 miles</field> <field value="500">within 500 miles</field> <field value="1000">within 1000 miles</field> <field value="0">anywhere</field> </enum> <enum name="drink"> <field value="5"></field> <field value="0">Never</field> <field value="4">Rarely</field> <field value="1">On occasion</field> <field value="2">Frequently</field> <field value="3">As much as possible</field> </enum> <enum name="education" show_values="true"> <field value="0">I'll tell you later</field> <field value="1">High School</field> <field value="2">Technical School</field> <field value="3">Associate degree</field> <field value="4">Professional degree</field> <field value="5">College Freshman</field> <field value="6">College Sophomore</field> <field value="7">College Junior</field> <field value="8">College Senior</field> <field value="9">Bachelor's degree</field> <field value="10">Graduate Student</field> <field value="11">Master's degree</field> <field value="12">Doctoral degree</field> </enum> <enum name="ethnicity"> <field value="9">African</field> <field value="0">Asian</field> <field value="1">Black / African descent</field> <field value="11">Caribbean</field> <field value="2">Caucasian / White</field> <field value="3">East Indian</field> <field value="4">Hispanic / Latino</field> <field value="5">Middle Eastern</field> <field value="6">Native American</field> <field value="7">Pacific Islands</field> <field value="8">Other</field> </enum> <enum name="gender"> <field value="-1">Select Gender</field> <field value="0">Male</field> <field value="1">Female</field> </enum> <enum name="marital_status"> <field value="6"></field> <field value="0">Single - never been married</field> <field value="2">Divorced</field> <field value="4">Legally separated</field> <field value="1">Widowed</field>  </enum> <enum name="religion"> <field value="28"></field> <field value="14">Agnostic</field> <field value="18">Anglican</field> <field value="19">Atheist</field> <field value="20">Bahai</field> <field value="21">Baptist</field> <field value="22">Born Again</field> <field value="11">Buddhist</field> <field value="0">Catholic</field> <field value="6">Christian</field> <field value="13">Confucian</field> <field value="5">Interdenominational</field> <field value="9">Hindu</field> <field value="8">Islamic / Muslim</field> <field value="7">Jewish</field> <field value="23">Lutheran</field> <field value="24">Methodist</field> <field value="2">Mormon/Latter-day Saint</field> <field value="3">Orthodox</field> <field value="10">Pagan/Wiccan/Umbanda</field> <field value="25">Presbyterian</field> <field value="1">Protestant</field> <field value="27">Seventh-Day Adventist</field> <field value="12">Taoist</field> <field value="4">Unitarian Universalist</field> <field value="26">Zoroastrian</field> <field value="15">I'm not affiliated with any religion</field> <field value="16">I'm not religious</field> <field value="17">Other</field> </enum> <enum name="smoke"> <field value="4"></field> <field value="0">Never</field> <field value="1">On occasion</field> <field value="2">Frequently</field> <field value="3">Whenever I can</field> </enum>  <enum name="food"> <field value="0">American</field> <field value="21">Argentine</field>  <field value="22">Barbeque</field> <field value="23">Brazilian</field> <field value="2">Cajun/Southern</field> <field value="24">Caribbean/Cuban</field> <field value="3">Chinese</field>  <field value="14">European</field> <field value="5">Fast food</field> <field value="6">French</field> <field value="7">German</field> <field value="8">Greek</field> <field value="25">Indian</field>  <field value="10">Italian</field> <field value="11">Japanese</field> <field value="26">Jewish/Kosher</field> <field value="19">Korean</field> <field value="27">Mediterranean</field>  <field value="13">Mexican</field> <field value="20">Persian</field>  <field value="15">Seafood</field> <field value="28">Southwestern</field> <field value="29">Spanish</field> <field value="16">Thai</field> <field value="30">Vegetarian/Vegan</field> <field value="17">Vietnamese</field>  </enum>  <enum name="indoors"> <field value="20">Aerobics</field> <field value="9">Art</field> <field value="1">Clubs</field> <field value="0">Cars</field> <field value="21">Charity/Volunteering</field> <field value="2">Collecting</field> <field value="12">Computers</field> <field value="4">Cooking/Culinary arts</field> <field value="5">Crafts</field> <field value="6">Dancing</field> <field value="17">Games</field> <field value="22">Martial Arts</field> <field value="18">Movies/TV</field> <field value="10">Music</field> <field value="16">Opera/Theater</field> <field value="23">Politics</field> <field value="13">Reading</field> <field value="24">Restaurants</field> <field value="14">Shopping</field> <field value="25">Sports</field> <field value="19">Weightlifting</field>  </enum>  <enum name="movie"> <field value="0">Action/Adventure</field> <field value="1">Cartoons</field> <field value="2">Comedy</field> <field value="3">Documentary</field> <field value="4">Drama</field> <field value="5">Foreign Films</field> <field value="6">Horror</field>  <field value="8">Musical</field> <field value="9">Mystery</field> <field value="10">Romance</field> <field value="11">Science Fiction</field> <field value="12">Suspense/Thriller</field> <field value="13">Western</field> </enum> <enum name="music"> <field value="27">Ambient</field> <field value="24">Blues</field> <field value="0">Classical</field> <field value="22">Classic Rock</field> <field value="1">Contemporary</field> <field value="2">Country/Western</field> <field value="3">Dance/Disco</field> <field value="4">Folk</field> <field value="23">Funk</field> <field value="26">Gospel</field> <field value="5">Heavy Metal</field> <field value="21">Hip Hop</field> <field value="6">Jazz</field> <field value="7">Movie Soundtracks</field> <field value="8">Newwave</field> <field value="9">Oldies</field> <field value="10">Opera</field> <field value="11">Pop/Top 40</field> <field value="12">Punk</field> <field value="20">Rap</field> <field value="13">Reggae</field> <field value="14">Religious Music</field> <field value="15">Rock n Roll</field> <field value="16">Soft Rock</field> <field value="17">Soul/Rhythm and Blues</field> <field value="18">Techno/Rave/Jungle</field> <field value="25">World</field>  </enum> <enum name="outdoors"> <field value="0">Automobiles/Motorcycles</field> <field value="20">Basketball</field> <field value="1">Biking</field> <field value="2">Birdwatching</field> <field value="3">Camping/Hiking/Fishing</field> <field value="4">Gardening</field> <field value="5">Golf</field> <field value="6">Horseback riding</field>  <field value="8">Photography</field> <field value="9">Running</field> <field value="10">Sailing</field> <field value="11">SCUBA Diving</field> <field value="12">Snow Skiing/Snowboarding</field> <field value="13">Sports/Athletics</field> <field value="14">Sunbathing</field> <field value="15">Surfing</field> <field value="16">Swimming</field> <field value="17">Tennis</field> <field value="18">Traveling/Sightseeing</field> <field value="19">Water Skiing/Windsurfing</field> </enum>  <enum name="political"> <field value="7"></field> <field value="0">Conservative</field> <field value="1">Into the wind</field> <field value="2">Liberal</field> <field value="6">Moderate</field> <field value="3">Open Minded</field> <field value="4">Socialist</field> <field value="5">Ultra Conservative</field> </enum>  <enum name="punctual"> <field value="5"></field> <field value="0">I am usually a little late</field> <field value="1">I am usually early</field> <field value="2">I am usually on time</field> <field value="3">I usually forget to show up</field> <field value="4">I'm star of the show.</field> </enum>  <enum name="trendy"> <field value="5"></field> <field value="0">I don't need advice!</field> <field value="1">I don't care about the latest fashions</field> <field value="2">I dress to be comfortable</field> <field value="3">I'm a somewhat fashionable person</field> <field value="4">I'm a very trendy person</field> </enum> <enum name="zodiac"> <field value="15"></field> <field value="0">Aquarius</field> <field value="1">Pisces</field> <field value="2">Aries</field> <field value="3">Gemini</field> <field value="4">Virgo</field> <field value="5">Capricorn</field> <field value="6">Cancer</field> <field value="7">Leo</field> <field value="8">Sagittarius</field> <field value="9">Libra</field> <field value="10">Scorpio</field> <field value="11">Taurus</field> <field value="12">Neon</field> <field value="13">Not sure</field> <field value="14">Not important to me</field> </enum> <enum name="children_at_home" show_values="true"> <field value="0">0</field> <field value="1">1</field> <field value="2">2</field> <field value="3">3</field> <field value="4">4</field> <field value="5">5+</field> </enum> <enum name="eyes"> <field value="9"></field> <field value="8">Black</field> <field value="0">Blue</field> <field value="1">Brown</field> <field value="2">Gray</field> <field value="3">Green</field> <field value="4">Hazel</field> <field value="5">Other</field> <field value="6">Red</field> <field value="7">Violet</field> </enum> <enum name="hair"> <field value="13"></field> <field value="12">Auburn</field> <field value="0">Black</field> <field value="1">Blonde</field> <field value="2">Dark Brown</field> <field value="3">Gray</field> <field value="4">Light Brown</field> <field value="5">Other</field> <field value="6">Red</field> <field value="7">Silver</field> <field value="8">Strawberry</field> <field value="9">Strawberry Blonde</field> <field value="10">White</field> <field value="11">Bald</field> </enum> <enum name="looking_for"> <field value="0">Dates</field>  <field value="2">Friends</field> <field value="3">Marriage</field>  </enum> <enum name="language"> <field value="18">Afrikaans</field> <field value="0">Arabic</field> <field value="1">Chinese</field> <field value="21">Dutch</field> <field value="2">English</field> <field value="3">Fijian</field> <field value="4">French</field> <field value="5">German</field> <field value="6">Greek</field> <field value="17">Hebrew</field> <field value="7">Italian</field> <field value="8">Japanese</field> <field value="19">Korean</field> <field value="9">Mandarin</field> <field value="20">Polish</field> <field value="11">Portuguese</field> <field value="12">Russian</field> <field value="13">Samoan</field> <field value="14">Spanish</field> <field value="15">Tongan</field> <field value="16">Other</field> </enum> <enum name="flags"> <field value="0">inactive_bonus</field> <field value="1">bad_email</field> </enum> <enum name="height" show_values="true"> <field value="36">3' 0" (91 cm)</field> <field value="37">3' 1" (93 cm)</field> <field value="38">3' 2" (96 cm)</field> <field value="39">3' 3" (99 cm)</field> <field value="40">3' 4" (101 cm)</field> <field value="41">3' 5" (104 cm)</field> <field value="42">3' 6" (106 cm)</field> <field value="43">3' 7" (109 cm)</field> <field value="44">3' 8" (111 cm)</field> <field value="45">3' 9" (114 cm)</field> <field value="46">3' 10" (116 cm)</field> <field value="47">3' 11" (119 cm)</field> <field value="48">4' 0" (121 cm)</field> <field value="49">4' 1" (124 cm)</field> <field value="50">4' 2" (127 cm)</field> <field value="51">4' 3" (129 cm)</field> <field value="52">4' 4" (132 cm)</field> <field value="53">4' 5" (134 cm)</field> <field value="54">4' 6" (137 cm)</field> <field value="55">4' 7" (139 cm)</field> <field value="56">4' 8" (142 cm)</field> <field value="57">4' 9" (144 cm)</field> <field value="58">4' 10" (147 cm)</field> <field value="59">4' 11" (149 cm)</field> <field value="60">5' 0" (152 cm)</field> <field value="61">5' 1" (154 cm)</field> <field value="62">5' 2" (157 cm)</field> <field value="63">5' 3" (160 cm)</field> <field value="64">5' 4" (162 cm)</field> <field value="65">5' 5" (165 cm)</field> <field value="66">5' 6" (167 cm)</field> <field value="67">5' 7" (170 cm)</field> <field value="68">5' 8" (172 cm)</field> <field value="69">5' 9" (175 cm)</field> <field value="70">5' 10" (177 cm)</field> <field value="71">5' 11" (180 cm)</field> <field value="72">6' 0" (182 cm)</field> <field value="73">6' 1" (185 cm)</field> <field value="74">6' 2" (187 cm)</field> <field value="75">6' 3" (190 cm)</field> <field value="76">6' 4" (193 cm)</field> <field value="77">6' 5" (195 cm)</field> <field value="78">6' 6" (198 cm)</field> <field value="79">6' 7" (200 cm)</field> <field value="80">6' 8" (203 cm)</field> <field value="81">6' 9" (205 cm)</field> <field value="82">6' 10" (208 cm)</field> <field value="83">6' 11" (210 cm)</field> <field value="84">7' 0" (213 cm)</field> <field value="85">7' 1" (215 cm)</field> <field value="86">7' 2" (218 cm)</field> <field value="87">7' 3" (220 cm)</field> <field value="88">7' 4" (223 cm)</field> <field value="89">7' 5" (226 cm)</field> <field value="90">7' 6" (228 cm)</field> </enum> <enum name="min_height"> <field value="36">3' 0" (91 cm)</field> <field value="37">3' 1" (93 cm)</field> <field value="38">3' 2" (96 cm)</field> <field value="39">3' 3" (99 cm)</field> <field value="40">3' 4" (101 cm)</field> <field value="41">3' 5" (104 cm)</field> <field value="42">3' 6" (106 cm)</field> <field value="43">3' 7" (109 cm)</field> <field value="44">3' 8" (111 cm)</field> <field value="45">3' 9" (114 cm)</field> <field value="46">3' 10" (116 cm)</field> <field value="47">3' 11" (119 cm)</field> <field value="48">4' 0" (121 cm)</field> <field value="49">4' 1" (124 cm)</field> <field value="50">4' 2" (127 cm)</field> <field value="51">4' 3" (129 cm)</field> <field value="52">4' 4" (132 cm)</field> <field value="53">4' 5" (134 cm)</field> <field value="54">4' 6" (137 cm)</field> <field value="55">4' 7" (139 cm)</field> <field value="56">4' 8" (142 cm)</field> <field value="57">4' 9" (144 cm)</field> <field value="58">4' 10" (147 cm)</field> <field value="59">4' 11" (149 cm)</field> <field value="60">5' 0" (152 cm)</field> <field value="61">5' 1" (154 cm)</field> <field value="62">5' 2" (157 cm)</field> <field value="63">5' 3" (160 cm)</field> <field value="64">5' 4" (162 cm)</field> <field value="65">5' 5" (165 cm)</field> <field value="66">5' 6" (167 cm)</field> <field value="67">5' 7" (170 cm)</field> <field value="68">5' 8" (172 cm)</field> <field value="69">5' 9" (175 cm)</field> <field value="70">5' 10" (177 cm)</field> <field value="71">5' 11" (180 cm)</field> <field value="72">6' 0" (182 cm)</field> <field value="73">6' 1" (185 cm)</field> <field value="74">6' 2" (187 cm)</field> <field value="75">6' 3" (190 cm)</field> <field value="76">6' 4" (193 cm)</field> <field value="77">6' 5" (195 cm)</field> <field value="78">6' 6" (198 cm)</field> <field value="79">6' 7" (200 cm)</field> <field value="80">6' 8" (203 cm)</field> <field value="81">6' 9" (205 cm)</field> <field value="82">6' 10" (208 cm)</field> <field value="83">6' 11" (210 cm)</field> <field value="84">7' 0" (213 cm)</field> <field value="85">7' 1" (215 cm)</field> <field value="86">7' 2" (218 cm)</field> <field value="87">7' 3" (220 cm)</field> <field value="88">7' 4" (223 cm)</field> <field value="89">7' 5" (226 cm)</field> <field value="90">7' 6" (228 cm)</field> </enum> <enum name="max_height"> <field value="36">3' 0" (91 cm)</field> <field value="37">3' 1" (93 cm)</field> <field value="38">3' 2" (96 cm)</field> <field value="39">3' 3" (99 cm)</field> <field value="40">3' 4" (101 cm)</field> <field value="41">3' 5" (104 cm)</field> <field value="42">3' 6" (106 cm)</field> <field value="43">3' 7" (109 cm)</field> <field value="44">3' 8" (111 cm)</field> <field value="45">3' 9" (114 cm)</field> <field value="46">3' 10" (116 cm)</field> <field value="47">3' 11" (119 cm)</field> <field value="48">4' 0" (121 cm)</field> <field value="49">4' 1" (124 cm)</field> <field value="50">4' 2" (127 cm)</field> <field value="51">4' 3" (129 cm)</field> <field value="52">4' 4" (132 cm)</field> <field value="53">4' 5" (134 cm)</field> <field value="54">4' 6" (137 cm)</field> <field value="55">4' 7" (139 cm)</field> <field value="56">4' 8" (142 cm)</field> <field value="57">4' 9" (144 cm)</field> <field value="58">4' 10" (147 cm)</field> <field value="59">4' 11" (149 cm)</field> <field value="60">5' 0" (152 cm)</field> <field value="61">5' 1" (154 cm)</field> <field value="62">5' 2" (157 cm)</field> <field value="63">5' 3" (160 cm)</field> <field value="64">5' 4" (162 cm)</field> <field value="65">5' 5" (165 cm)</field> <field value="66">5' 6" (167 cm)</field> <field value="67">5' 7" (170 cm)</field> <field value="68">5' 8" (172 cm)</field> <field value="69">5' 9" (175 cm)</field> <field value="70">5' 10" (177 cm)</field> <field value="71">5' 11" (180 cm)</field> <field value="72">6' 0" (182 cm)</field> <field value="73">6' 1" (185 cm)</field> <field value="74">6' 2" (187 cm)</field> <field value="75">6' 3" (190 cm)</field> <field value="76">6' 4" (193 cm)</field> <field value="77">6' 5" (195 cm)</field> <field value="78">6' 6" (198 cm)</field> <field value="79">6' 7" (200 cm)</field> <field value="80">6' 8" (203 cm)</field> <field value="81">6' 9" (205 cm)</field> <field value="82">6' 10" (208 cm)</field> <field value="83">6' 11" (210 cm)</field> <field value="84">7' 0" (213 cm)</field> <field value="85">7' 1" (215 cm)</field> <field value="86">7' 2" (218 cm)</field> <field value="87">7' 3" (220 cm)</field> <field value="88">7' 4" (223 cm)</field> <field value="89">7' 5" (226 cm)</field> <field value="90">7' 6" (228 cm)</field> </enum> <enum name="min_age"> <field value="-1"/> <field value="18">18</field> <field value="19">19</field> <field value="20">20</field> <field value="21">21</field> <field value="22">22</field> <field value="23">23</field> <field value="24">24</field> <field value="25">25</field> <field value="26">26</field> <field value="27">27</field> <field value="28">28</field> <field value="29">29</field> <field value="30">30</field> <field value="31">31</field> <field value="32">32</field> <field value="33">33</field> <field value="34">34</field> <field value="35">35</field> <field value="36">36</field> <field value="37">37</field> <field value="38">38</field> <field value="39">39</field> <field value="40">40</field> <field value="41">41</field> <field value="42">42</field> <field value="43">43</field> <field value="44">44</field> <field value="45">45</field> <field value="46">46</field> <field value="47">47</field> <field value="48">48</field> <field value="49">49</field> <field value="50">50</field> <field value="51">51</field> <field value="52">52</field> <field value="53">53</field> <field value="54">54</field> <field value="55">55</field> <field value="56">56</field> <field value="57">57</field> <field value="58">58</field> <field value="59">59</field> <field value="60">60</field> <field value="61">61</field> <field value="62">62</field> <field value="63">63</field> <field value="64">64</field> <field value="65">65</field> <field value="66">66</field> <field value="67">67</field> <field value="68">68</field> <field value="69">69</field> <field value="70">70</field> <field value="71">71</field> <field value="72">72</field> <field value="73">73</field> <field value="74">74</field> <field value="75">75</field> <field value="76">76</field> <field value="77">77</field> <field value="78">78</field> <field value="79">79</field> <field value="80">80</field> <field value="81">81</field> <field value="82">82</field> <field value="83">83</field> <field value="84">84</field> <field value="85">85</field> <field value="86">86</field> <field value="87">87</field> <field value="88">88</field> <field value="89">89</field> <field value="90">90</field> <field value="91">91</field> <field value="92">92</field> <field value="93">93</field> <field value="94">94</field> <field value="95">95</field> <field value="96">96</field> <field value="97">97</field> <field value="98">98</field> <field value="99">99</field> <field value="100">100</field> <field value="101">101</field> <field value="102">102</field> <field value="103">103</field> <field value="104">104</field> <field value="105">105</field> <field value="106">106</field> <field value="107">107</field> <field value="108">108</field> <field value="109">109</field> </enum> <enum name="max_age"> <field value="-1"/> <field value="18">18</field> <field value="19">19</field> <field value="20">20</field> <field value="21">21</field> <field value="22">22</field> <field value="23">23</field> <field value="24">24</field> <field value="25">25</field> <field value="26">26</field> <field value="27">27</field> <field value="28">28</field> <field value="29">29</field> <field value="30">30</field> <field value="31">31</field> <field value="32">32</field> <field value="33">33</field> <field value="34">34</field> <field value="35">35</field> <field value="36">36</field> <field value="37">37</field> <field value="38">38</field> <field value="39">39</field> <field value="40">40</field> <field value="41">41</field> <field value="42">42</field> <field value="43">43</field> <field value="44">44</field> <field value="45">45</field> <field value="46">46</field> <field value="47">47</field> <field value="48">48</field> <field value="49">49</field> <field value="50">50</field> <field value="51">51</field> <field value="52">52</field> <field value="53">53</field> <field value="54">54</field> <field value="55">55</field> <field value="56">56</field> <field value="57">57</field> <field value="58">58</field> <field value="59">59</field> <field value="60">60</field> <field value="61">61</field> <field value="62">62</field> <field value="63">63</field> <field value="64">64</field> <field value="65">65</field> <field value="66">66</field> <field value="67">67</field> <field value="68">68</field> <field value="69">69</field> <field value="70">70</field> <field value="71">71</field> <field value="72">72</field> <field value="73">73</field> <field value="74">74</field> <field value="75">75</field> <field value="76">76</field> <field value="77">77</field> <field value="78">78</field> <field value="79">79</field> <field value="80">80</field> <field value="81">81</field> <field value="82">82</field> <field value="83">83</field> <field value="84">84</field> <field value="85">85</field> <field value="86">86</field> <field value="87">87</field> <field value="88">88</field> <field value="89">89</field> <field value="90">90</field> <field value="91">91</field> <field value="92">92</field> <field value="93">93</field> <field value="94">94</field> <field value="95">95</field> <field value="96">96</field> <field value="97">97</field> <field value="98">98</field> <field value="99">99</field> <field value="100">100</field> <field value="101">101</field> <field value="102">102</field> <field value="103">103</field> <field value="104">104</field> <field value="105">105</field> <field value="106">106</field> <field value="107">107</field> <field value="108">108</field> <field value="109">109</field> </enum> <enum name="auto_email"> <field value="50">50%</field> <field value="60">60%</field> <field value="70">70%</field> <field value="80">80%</field> <field value="90">90%</field> <field value="100">100%</field> </enum> <enum name="importance"> <field value="0">Not important</field> <field value="1">Desirable</field> <field value="2">Somewhat important</field> <field value="3">Important</field> <field value="4">Very important</field> </enum> <enum name="age"> <field value="-1"/> <field value="18">18</field> <field value="19">19</field> <field value="20">20</field> <field value="21">21</field> <field value="22">22</field> <field value="23">23</field> <field value="24">24</field> <field value="25">25</field> <field value="26">26</field> <field value="27">27</field> <field value="28">28</field> <field value="29">29</field> <field value="30">30</field> <field value="31">31</field> <field value="32">32</field> <field value="33">33</field> <field value="34">34</field> <field value="35">35</field> <field value="36">36</field> <field value="37">37</field> <field value="38">38</field> <field value="39">39</field> <field value="40">40</field> <field value="41">41</field> <field value="42">42</field> <field value="43">43</field> <field value="44">44</field> <field value="45">45</field> <field value="46">46</field> <field value="47">47</field> <field value="48">48</field> <field value="49">49</field> <field value="50">50</field> <field value="51">51</field> <field value="52">52</field> <field value="53">53</field> <field value="54">54</field> <field value="55">55</field> <field value="56">56</field> <field value="57">57</field> <field value="58">58</field> <field value="59">59</field> <field value="60">60</field> <field value="61">61</field> <field value="62">62</field> <field value="63">63</field> <field value="64">64</field> <field value="65">65</field> <field value="66">66</field> <field value="67">67</field> <field value="68">68</field> <field value="69">69</field> <field value="70">70</field> <field value="71">71</field> <field value="72">72</field> <field value="73">73</field> <field value="74">74</field> <field value="75">75</field> <field value="76">76</field> <field value="77">77</field> <field value="78">78</field> <field value="79">79</field> <field value="80">80</field> <field value="81">81</field> <field value="82">82</field> <field value="83">83</field> <field value="84">84</field> <field value="85">85</field> <field value="86">86</field> <field value="87">87</field> <field value="88">88</field> <field value="89">89</field> <field value="90">90</field> <field value="91">91</field> <field value="92">92</field> <field value="93">93</field> <field value="94">94</field> <field value="95">95</field> <field value="96">96</field> <field value="97">97</field> <field value="98">98</field> <field value="99">99</field> <field value="100">100</field> <field value="101">101</field> <field value="102">102</field> <field value="103">103</field> <field value="104">104</field> <field value="105">105</field> <field value="106">106</field> <field value="107">107</field> <field value="108">108</field> <field value="109">109</field> </enum> </strings>

Parse XML file to read in SQL

DECLARE @hdoc int
DECLARE @doc nvarchar(max)
SelecT @doc = convert(nvarchar(max),xml_data) from XmlImportTest

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

--select @hdoc

SELECT *
FROM OPENXML (@hdoc, '/strings/enum')
with (name varchar(100) '@name')

SELECT *
FROM OPENXML (@hdoc, '/strings/enum/field')
with ( name varchar(100) '../@name',
val varchar(100) '@value',
txt varchar(100) '.')

-- Remove the internal representation.
exec sp_xml_removedocument @hdoc

Import XML data into a table

CREATE TABLE XmlImportTest(
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'C:\Documents and Settings\palexander\Desktop\Mingle Meta Data\black_strings.xml'

--dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

EXEC('INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO

Tuesday, March 31, 2009

How to Identify Blocking Problems with SQL Profiler

Thursday, March 26, 2009

Detach all databases on a server

use master
go

exec master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use master''
print ''go''
print ''EXEC master.dbo.sp_detach_db @dbname = ''''?'''', @keepfulltextindexfile=N''''true''''''
print ''go''
end
'
go

Using SP_MSForEachDB and SP_MSForEachTable

Tuesday, March 24, 2009

Insert into an IDENTITY column Table

SET IDENTITY_INSERT MemberMailID ON;
insert into MemberMailID (pkid,inputvalue,insertdate) values(589297975,1,getdate());
SET IDENTITY_INSERT MemberMailID OFF;

create table #tmp (lastid int)

declare @MemberMailID int
insert into #tmp
exec up_PrimaryKey_Select 'MemberMailID'
select @MemberMailID = lastid from #tmp
select @MemberMailID

-------------------------------------

ALTER proc [dbo].[up_PrimaryKey_Select] @KeyName varchar(75),@InputValue int = null
as
set nocount on
declare @ExecString nvarchar (1000)
declare @PKID int
if @InputValue is null
begin
set @InputValue = 1
end
set
set @ExecString = 'insert '+@KeyName+'(InputValue) values(@InputValue)
select @PKID = scope_identity()'
exec sp_executesql @ExecString,
N'@InputValue int,@PKID int output',@InputValue, @PKID = @PKID output
select @PKID as PKID
return

Friday, March 13, 2009

Finding the Worst Performing T-SQL Statements on an Instance

Introduction to Attribute Discretization

Thursday, March 12, 2009

SQL SERVER - Guidelines and Coding Standards

Thursday, March 05, 2009

Find a Stored Procedure within entire server

EXEC master..sp_MSForEachDB '
IF EXISTS
(
SELECT 1
FROM [?].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ''up_AggregateStatistics_SingleDate''
)
BEGIN
SELECT ''[?]'';

SELECT ''['' + SPECIFIC_CATALOG + '']'', ''['' + ROUTINE_NAME + '']''
FROM [?].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ''up_AggregateStatistics_SingleDate'';
END';

Thursday, February 12, 2009

Change Job Owner

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_id=N'9b72dfd8-0845-4632-8ffb-43d35e723ace', @owner_login_name=N'sa'
GO

--select * from msdb.dbo.sysjobs where job_id=N'9b72dfd8-0845-4632-8ffb-43d35e723ace'

Purge MSDB Mail History

use msdb
go

DECLARE @d datetime
select @d = min(last_mod_date) from sysmail_attachments with (nolock)
select @d
set @d = dateadd(dd,1,@d)
select @d

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @d
go

DBCC SHRINKFILE (N'MSDBLog' , 0)
GO
DBCC SHRINKDATABASE(N'msdb' )
GO

/*
select top 10 * from sysmail_attachments with (nolock)
select count(*) from sysmail_attachments with (nolock)
select min(last_mod_date) from sysmail_attachments with (nolock)
select max(last_mod_date) from sysmail_attachments with (nolock)
*/

Wednesday, January 28, 2009

Grant Permission to View Stored Procedures - Dynamic Creation for all DBs

master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)''
print ''set @user = ''''SPARKSTAGE\developers''''''
print ''DECLARE Cur_name CURSOR FOR ''
print ''select specific_name From information_Schema.routines''
print ''OPEN Cur_name''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''WHILE @@FETCH_STATUS = 0''
print ''BEGIN''
print ''set @string =''''GRANT VIEW DEFINITION ON [dbo].[''''+@name+''''] TO [''''+@user+'''']''''''
print ''print @string''
print ''exec sp_executesql @string''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''END''
print ''CLOSE Cur_name''
print ''DEALLOCATE Cur_name''
print ''go''
end
'
go

Grant View Permission on Stored Procedures

--select * from information_Schema.routines
--select specific_name from information_Schema.routines

declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'SPARKSTAGE\developers'

DECLARE Cur_name CURSOR FOR
select specific_name From information_Schema.routines

OPEN Cur_name
FETCH NEXT FROM Cur_name INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
print '================================='
print @name
print '================================='
set @string ='GRANT VIEW DEFINITION ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string

FETCH NEXT FROM Cur_name INTO @name
END
CLOSE Cur_name
DEALLOCATE Cur_name

Thursday, January 22, 2009

Extract Text after 2 commas in a string

select MemberGroupLangStr
, CHARINDEX(',',MemberGroupLangStr)
, substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr))
, CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr)))
, CHARINDEX(',',MemberGroupLangStr) + CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr)))
, substring(MemberGroupLangStr , CHARINDEX(',',MemberGroupLangStr) + CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr))) + 1 , len(MemberGroupLangStr))
From MembersWithUnapprovedTextAttributes

Extract Text between two commas

select MemberGroupLangStr,
CASE
-- when no second semi-colon,
WHEN CHARINDEX(',',(SUBSTRING(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr,1)+1,99))) = 0
-- then extract the full string from first semi-colon to the max length of 99
THEN LTRIM(RTRIM(SUBSTRING(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr,1)+1,99)))
-- else extract the full string from the first semi-colon up to second semi-colon
ELSE LTRIM(RTRIM(SUBSTRING(
/* > */ MemberGroupLangStr,
/* */ CHARINDEX(',',MemberGroupLangStr,1) + 1,
/* */ CHARINDEX(',', SUBSTRING(MemberGroupLangStr,
CHARINDEX(',', MemberGroupLangStr, 1) + 1,
99)) - 1
) ) )
END AS Result_string
From MembersWithUnapprovedTextAttributes

Tuesday, January 20, 2009

Grant access to database in a server for domain user

EXEC master..sp_MSForEachDB '

if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''CREATE USER [Domain\user] FOR LOGIN [Domain\user]''
print ''go''
print ''EXEC sp_addrolemember N''''db_datareader'''', N''''Domain\user''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_executor'''', N''''Domain\user''''''
print ''go''

end
'
go

Friday, January 16, 2009

List objects per filegroup

IF OBJECT_ID('tempdb.dbo.#FileGroup') IS NOT NULL

DROP TABLE #FileGroup

IF OBJECT_ID('tempdb.dbo.#ObjectFileGroup') IS NOT NULL

DROP TABLE #ObjectFileGroup

CREATE TABLE #FileGroup (

FileGroup sysname

)

CREATE TABLE #ObjectFileGroup (

ObjectName sysname,

ObjectType varchar(20),

FileGroupID int,

FileGroup sysname

)

SET NOCOUNT ON

DECLARE @TableName sysname

DECLARE @id int

DECLARE cur_Tables CURSOR FAST_FORWARD FOR

SELECT TableName = [name], id FROM dbo.sysobjects WHERE type = 'U'

OPEN cur_Tables

FETCH NEXT FROM cur_Tables INTO @TableName, @id

WHILE @@FETCH_STATUS = 0

BEGIN

TRUNCATE TABLE #FileGroup

INSERT #FileGroup (FileGroup)

EXEC sp_objectfilegroup @id

INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)

SELECT @TableName, 'TABLE', FILEGROUP_ID(FileGroup), FileGroup

FROM #FileGroup

FETCH NEXT FROM cur_Tables INTO @TableName, @id

END

CLOSE cur_Tables

DEALLOCATE cur_Tables

INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)

SELECT OBJECT_NAME(id) + ' * ' +[name], 'INDEX', groupid, FILEGROUP_NAME(groupid) FROM dbo.sysindexes

WHERE FILEGROUP_NAME(groupid) IS NOT NULL

AND OBJECT_NAME(id) NOT LIKE 'sys%'

AND [name] NOT LIKE '_WA_Sys%'

AND [name] NOT LIKE 'Statistic_%'

SELECT FileGroupName = FILEGROUP_NAME(sf.groupid),/*ofg.FileGroup, */ofg.ObjectName, ofg.ObjectType, FileName = sf.filename, FileSize = sf.[size] / 128

FROM #ObjectFileGroup ofg

RIGHT JOIN dbo.sysfiles sf

ON ofg.FileGroupID = sf.groupid

ORDER BY FileGroup, ObjectName

SQL 2005 (very simplified)

SELECT

fg.data_space_id, fg.name,

ObjectName = OBJECT_NAME(p.object_id), p.index_id

,df.name, df.physical_name, [Size] = df.size*8/1024

FROM sys.filegroups fg

LEFT JOIN sys.database_files df

ON fg.data_space_id = df.data_space_id

LEFT JOIN sys.partitions p

ON fg.data_space_id = p.partition_number

WHERE (p.object_id>4096 or p.object_id IS NULL)

Tuesday, January 13, 2009

Change all db owners to 'sa'

sp_helpdb
go
EXEC master..sp_MSForEachDB '

if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''exec sp_changedbowner sa''
print ''go''
end
'
go

sp_helpdb
go