Rob's SQL Server Blog
Helpful Blog
Friday, May 29, 2009
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
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
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
go
exec sp_dropserver 'CLSQL106'
go
sp_addserver 'CLSQL64', 'local'
go
Restart the server
Wednesday, April 08, 2009
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];
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
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
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
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
Subscribe to:
Posts (Atom)