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

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

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

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

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

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

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

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