Wednesday, September 29, 2010
Thursday, September 23, 2010
Full Text Search
--Create FTS
Use ClientDB
GO
EXEC sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG [FTC_LO_TEXT] WITH ACCENT_SENSITIVITY = OFF
GO
CREATE FULLTEXT INDEX ON [dbo].[lo_text] KEY INDEX [PK_lo_text] ON ([FTC_LO_TEXT]) WITH CHANGE_TRACKING AUTO)
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([descr])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([keywords])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([title])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ENABLE
GO
--Drop FTS
EXEC sp_fulltext_database 'disable'
GO
DROP FULLTEXT INDEX ON lo_text
GO
IF EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'FTC_LO_TEXT')
DROP FULLTEXT CATALOG [FTC_LO_TEXT]''
GO
--Disable Stopwords and Stoplists on FTS
ALTER FULLTEXT INDEX ON lo_text
SET STOPLIST OFF;
GO
EXEC sp_fulltext_catalog 'FTC_LO_TEXT', 'rebuild'
GO
--Rebuild FTS Catalog
ALTER FULLTEXT CATALOG FTC_LO_TEXT REBUILD
--Sample of FTS search
declare @str_decr nvarchar(50), @str nvarchar(50) , @str1 nvarchar(50) , @str2 nvarchar(50)
set @str1 = 'New'
set @str2 = 'Document'
set @str_decr = '%'+@str1+'%'
select * From lo_text lt where lt.title LIKE @str_decr-- or lt.descr LIKE @str_decr
set @str = @str1 + ' OR ' +@str2
select * from lo_text WHERE CONTAINS(title, @str)
set @str = @str1 + ' AND ' +@str2
select * from lo_text WHERE CONTAINS(title, @str)
set @str = @str1 + ' ' +@str2
select * from lo_text WHERE FREETEXT(*, @str)
set @str = '"'+@str1 + ' ' +@str2+'*"'
select * from lo_text WHERE FREETEXT(descr, @str)
NOTES:
1)If you cannot start the fdhost, please check the permission of FDLauncher Service logon account. Do you specify a user account for FDLauncher service during setup? If you didn't specify user account for FDLauncher Service, by default, FDLauncher is configured using the Local Service account. This account is not added to the SQLServerFDHostUser$$MSSQLSERVER group. To work around the issue, you need to assign Local Service account (or logon account of FDLauncher Service) to SQLServerFDHostUser$$MSSQLSERVER group. Then restart fdhost using sp_fulltext_service 'restart_all_fdhosts' procedure.
EXEC sp_fulltext_service 'restart_all_fdhosts'
Use ClientDB
GO
EXEC sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG [FTC_LO_TEXT] WITH ACCENT_SENSITIVITY = OFF
GO
CREATE FULLTEXT INDEX ON [dbo].[lo_text] KEY INDEX [PK_lo_text] ON ([FTC_LO_TEXT]) WITH CHANGE_TRACKING AUTO)
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([descr])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([keywords])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([title])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ENABLE
GO
--Drop FTS
EXEC sp_fulltext_database 'disable'
GO
DROP FULLTEXT INDEX ON lo_text
GO
IF EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'FTC_LO_TEXT')
DROP FULLTEXT CATALOG [FTC_LO_TEXT]''
GO
--Disable Stopwords and Stoplists on FTS
ALTER FULLTEXT INDEX ON lo_text
SET STOPLIST OFF;
GO
EXEC sp_fulltext_catalog 'FTC_LO_TEXT', 'rebuild'
GO
--Rebuild FTS Catalog
ALTER FULLTEXT CATALOG FTC_LO_TEXT REBUILD
--Sample of FTS search
declare @str_decr nvarchar(50), @str nvarchar(50) , @str1 nvarchar(50) , @str2 nvarchar(50)
set @str1 = 'New'
set @str2 = 'Document'
set @str_decr = '%'+@str1+'%'
select * From lo_text lt where lt.title LIKE @str_decr-- or lt.descr LIKE @str_decr
set @str = @str1 + ' OR ' +@str2
select * from lo_text WHERE CONTAINS(title, @str)
set @str = @str1 + ' AND ' +@str2
select * from lo_text WHERE CONTAINS(title, @str)
set @str = @str1 + ' ' +@str2
select * from lo_text WHERE FREETEXT(*, @str)
set @str = '"'+@str1 + ' ' +@str2+'*"'
select * from lo_text WHERE FREETEXT(descr, @str)
NOTES:
1)If you cannot start the fdhost, please check the permission of FDLauncher Service logon account. Do you specify a user account for FDLauncher service during setup? If you didn't specify user account for FDLauncher Service, by default, FDLauncher is configured using the Local Service account. This account is not added to the SQLServerFDHostUser$
EXEC sp_fulltext_service 'restart_all_fdhosts'
Tuesday, September 14, 2010
Restore db from backup file with dynamic build
-----------------------------------------------------------------------
--RESTORE DB
-----------------------------------------------------------------------
use master
go
declare @backupfile nvarchar(200) , @sql nvarchar(1000), @restorepath nvarchar(100), @restoredbname nvarchar(100) ,@logicalname nvarchar(100), @ttype nvarchar(10)
set @backupfile = 'e:\ces_uws_backup_2010_09_13_182228_1716321.bak'
set @restorepath= 'E:\DATA\'
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
create table #tmp
(LogicalName nvarchar(100),
PhysicalName nvarchar(500),
Type nvarchar(2),
FileGroupName nvarchar(20),
Size bigint ,
MaxSize bigint ,
FileId int ,
CreateLSN int,
DropLSN int,
UniqueId uniqueidentifier,
ReadOnlyLSN int,
ReadWriteLSN int,
BackupSizeInBytes bigint,
SourceBlockSize bigint,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN nvarchar(100),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly int,
IsPresent int,
TDEThumbprint nvarchar(100))
set @sql = 'RESTORE FILELISTONLY from disk = '''+@backupfile+''''
print @sql
insert into #tmp
exec sp_executesql @sql
select * from #tmp
if(OBJECT_ID('tempdb..#tmp1')>1)
drop table #tmp1
create table #tmp1
(BackupName nvarchar(500),
BackupDescription nvarchar(500),
BackupType int,
ExpirationDate datetime,
Compressed int,
Position int ,
DeviceType int,
UserName nvarchar(100),
ServerName nvarchar(100),
DatabaseName nvarchar(100),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize bigint,
FirstLSN nvarchar(100),
LastLSN nvarchar(100),
CheckpointLSN nvarchar(100),
DatabaseBackupLSN nvarchar(100),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder int,
CodePage int,
UnicodeLocaleId int ,
UnicodeComparisonStyle int,
CompatibilityLevel int,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int ,
SoftwareVersionBuild int ,
MachineName nvarchar(100),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(100),
FamilyGUID uniqueidentifier,
HasBulkLoggedData datetime,
IsSnapshot int,
IsReadOnly int ,
IsSingleUser int,
HasBackupChecksums int,
IsDamaged int,
BeginsLogChain int,
HasIncompleteMetaData int,
IsForceOffline int,
IsCopyOnly int,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN nvarchar(100),
RecoveryModel nvarchar(100),
DifferentialBaseLSN nvarchar(100),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(500),
BackupSetGUID uniqueidentifier,
CompressedBackupSize bigint )
set @sql = 'restore headeronly from disk = '''+@backupfile+''' with nounload'
print @sql
insert into #tmp1
exec sp_executesql @sql
select * from #tmp1
select @restoredbname = replace(DatabaseName,'ces_','')+'_'+convert(nvarchar(4),year(BackupFinishDate))+'_'+convert(nvarchar(4),MONTH(BackupFinishDate))+'_'+convert(nvarchar(4),DAY(BackupFinishDate))
from #tmp1
select @restoredbname
set @sql = 'RESTORE DATABASE '+@restoredbname+' from disk = '''+@backupfile+'''
WITH FILE = 1, '
declare tmp_cur cursor for
select logicalname, type from #tmp order by fileid
open tmp_cur
Fetch next from tmp_cur into @logicalname,@ttype
while @@fetch_status = 0
begin
--print @logicalname
--print @ttype
set @sql = @sql + 'MOVE '''+@logicalname+''' TO '''+@restorepath+@restoredbname+(case when @ttype = 'D' then '.mdf' when @ttype = 'L' then '.ldf' else '.ndf' end )+''','
Fetch next from tmp_cur into @logicalname, @ttype
--print '---'
end
close tmp_cur
deallocate tmp_cur
set @sql = @sql + ' NOUNLOAD, REPLACE, STATS = 10'
print @sql
exec sp_executesql @sql
set @sql = 'ALTER DATABASE '+@restoredbname+' SET RECOVERY SIMPLE'
print @sql
exec sp_executesql @sql
set @sql = 'USE '+@restoredbname+'
DBCC SHRINKFILE (2,0)
'
print @sql
exec sp_executesql @sql
set @sql = 'update '+@restoredbname+'.dbo.corp_setting set [value] = ''deadbox@cyberu.com'' where [key] = ''OverrideEmailAddress'''
print @sql
exec sp_executesql @sql
--RESTORE DB
-----------------------------------------------------------------------
use master
go
declare @backupfile nvarchar(200) , @sql nvarchar(1000), @restorepath nvarchar(100), @restoredbname nvarchar(100) ,@logicalname nvarchar(100), @ttype nvarchar(10)
set @backupfile = 'e:\ces_uws_backup_2010_09_13_182228_1716321.bak'
set @restorepath= 'E:\DATA\'
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
create table #tmp
(LogicalName nvarchar(100),
PhysicalName nvarchar(500),
Type nvarchar(2),
FileGroupName nvarchar(20),
Size bigint ,
MaxSize bigint ,
FileId int ,
CreateLSN int,
DropLSN int,
UniqueId uniqueidentifier,
ReadOnlyLSN int,
ReadWriteLSN int,
BackupSizeInBytes bigint,
SourceBlockSize bigint,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN nvarchar(100),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly int,
IsPresent int,
TDEThumbprint nvarchar(100))
set @sql = 'RESTORE FILELISTONLY from disk = '''+@backupfile+''''
print @sql
insert into #tmp
exec sp_executesql @sql
select * from #tmp
if(OBJECT_ID('tempdb..#tmp1')>1)
drop table #tmp1
create table #tmp1
(BackupName nvarchar(500),
BackupDescription nvarchar(500),
BackupType int,
ExpirationDate datetime,
Compressed int,
Position int ,
DeviceType int,
UserName nvarchar(100),
ServerName nvarchar(100),
DatabaseName nvarchar(100),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize bigint,
FirstLSN nvarchar(100),
LastLSN nvarchar(100),
CheckpointLSN nvarchar(100),
DatabaseBackupLSN nvarchar(100),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder int,
CodePage int,
UnicodeLocaleId int ,
UnicodeComparisonStyle int,
CompatibilityLevel int,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int ,
SoftwareVersionBuild int ,
MachineName nvarchar(100),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(100),
FamilyGUID uniqueidentifier,
HasBulkLoggedData datetime,
IsSnapshot int,
IsReadOnly int ,
IsSingleUser int,
HasBackupChecksums int,
IsDamaged int,
BeginsLogChain int,
HasIncompleteMetaData int,
IsForceOffline int,
IsCopyOnly int,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN nvarchar(100),
RecoveryModel nvarchar(100),
DifferentialBaseLSN nvarchar(100),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(500),
BackupSetGUID uniqueidentifier,
CompressedBackupSize bigint )
set @sql = 'restore headeronly from disk = '''+@backupfile+''' with nounload'
print @sql
insert into #tmp1
exec sp_executesql @sql
select * from #tmp1
select @restoredbname = replace(DatabaseName,'ces_','')+'_'+convert(nvarchar(4),year(BackupFinishDate))+'_'+convert(nvarchar(4),MONTH(BackupFinishDate))+'_'+convert(nvarchar(4),DAY(BackupFinishDate))
from #tmp1
select @restoredbname
set @sql = 'RESTORE DATABASE '+@restoredbname+' from disk = '''+@backupfile+'''
WITH FILE = 1, '
declare tmp_cur cursor for
select logicalname, type from #tmp order by fileid
open tmp_cur
Fetch next from tmp_cur into @logicalname,@ttype
while @@fetch_status = 0
begin
--print @logicalname
--print @ttype
set @sql = @sql + 'MOVE '''+@logicalname+''' TO '''+@restorepath+@restoredbname+(case when @ttype = 'D' then '.mdf' when @ttype = 'L' then '.ldf' else '.ndf' end )+''','
Fetch next from tmp_cur into @logicalname, @ttype
--print '---'
end
close tmp_cur
deallocate tmp_cur
set @sql = @sql + ' NOUNLOAD, REPLACE, STATS = 10'
print @sql
exec sp_executesql @sql
set @sql = 'ALTER DATABASE '+@restoredbname+' SET RECOVERY SIMPLE'
print @sql
exec sp_executesql @sql
set @sql = 'USE '+@restoredbname+'
DBCC SHRINKFILE (2,0)
'
print @sql
exec sp_executesql @sql
set @sql = 'update '+@restoredbname+'.dbo.corp_setting set [value] = ''deadbox@cyberu.com'' where [key] = ''OverrideEmailAddress'''
print @sql
exec sp_executesql @sql
View Database Backup History
---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database
-------------------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name
-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database - Detailed
-------------------------------------------------------------------------------------------
SELECT
A.[Server],
A.database_name,
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A
LEFT JOIN
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database
-------------------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name
-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database - Detailed
-------------------------------------------------------------------------------------------
SELECT
A.[Server],
A.database_name,
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A
LEFT JOIN
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name
Monday, August 30, 2010
Presentation Links: SQL Server Performance Tuning (Quest)
http://blogs.msdn.com/b/buckwoody/archive/2010/07/21/presentation-links-sql-server-performance-tuning-quest.aspx
Friday, August 20, 2010
Grant create table and SP for schema
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'CSODMGMT\SQLREWR')
CREATE USER [CSODMGMT\SQLREWR] FOR LOGIN [CSODMGMT\SQLREWR]
GO
EXEC sp_addrolemember N'db_datareader', N'CSODMGMT\SQLREWR'
GO
EXEC sp_addrolemember N'db_datawriter', N'CSODMGMT\SQLREWR'
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SQLREWR')
DROP SCHEMA [SQLREWR]
GO
CREATE SCHEMA [SQLREWR] AUTHORIZATION [CSODMGMT\SQLREWR]
GO
GRANT CREATE TABLE TO [CSODMGMT\SQLREWR]
go
GRANT CREATE PROCEDURE TO [CSODMGMT\SQLREWR]
go
----------------------------------
declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'CSODMGMT\SQLREWR'
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
set @string ='GRANT EXECUTE 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
GO
CREATE USER [CSODMGMT\SQLREWR] FOR LOGIN [CSODMGMT\SQLREWR]
GO
EXEC sp_addrolemember N'db_datareader', N'CSODMGMT\SQLREWR'
GO
EXEC sp_addrolemember N'db_datawriter', N'CSODMGMT\SQLREWR'
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SQLREWR')
DROP SCHEMA [SQLREWR]
GO
CREATE SCHEMA [SQLREWR] AUTHORIZATION [CSODMGMT\SQLREWR]
GO
GRANT CREATE TABLE TO [CSODMGMT\SQLREWR]
go
GRANT CREATE PROCEDURE TO [CSODMGMT\SQLREWR]
go
----------------------------------
declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'CSODMGMT\SQLREWR'
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
set @string ='GRANT EXECUTE 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
GO
Wednesday, August 18, 2010
sp_msforeachdb
sp_msforeachdb
'
if ''?'' like ''ces_%''
begin
use [?]
print ''use [''+''?''+'']''
print ''GO''
print ''DROP FULLTEXT INDEX ON lo_text''
print ''go''
end
'
'
if ''?'' like ''ces_%''
begin
use [?]
print ''use [''+''?''+'']''
print ''GO''
print ''DROP FULLTEXT INDEX ON lo_text''
print ''go''
end
'
Monday, August 09, 2010
Find Currently Running Query
-- ALL Sessions
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
-- SPECIFIC SESSION DETAIL
dbcc inputbuffer(59)
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
-- SPECIFIC SESSION DETAIL
dbcc inputbuffer(59)
Monday, July 26, 2010
Implementing the OUTPUT Clause in SQL Server 2008
http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/?utm_source=simpletalk&utm_medium=email-main&utm_content=OutputClause-20100726&utm_campaign=SQL
Subscribe to:
Posts (Atom)