My Daily Dose of SQL Server
sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GOExcel
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 GOStep 2:
SELECT * INTO dbo.ExcelWorksheetContacts
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=NO;Database=E:\SQLCAT\book1.xlsx;',
[Contacts$]
)
2. To Import Data from Microsoft Office Excel 97-2003 Worksheet(.xls) SELECT * INTO dbo.ExcelWorksheetContacts
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=E:\SQLCAT\ExcelWorksheet.xls',
'Select FirstName,LastName,DateofBirth,Address1,City,State,ZipCode From [Contacts$]')
SELECT * INTO dbo.ExcelWorksheetContacts
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\SQLCAT\ExcelWorksheet.xls;HDR=yes',
'SELECT * FROM [Contacts$A1:C3]'
)
4. To Export Data in Excel (Already Have format) from SQL Table INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\SQLCAT\ExcelWorksheet.xls;HDR=yes',
'SELECT * FROM [Contacts$]') SELECT * FROM dbo.ExcelWorksheetContacts
DECLARE @sql VARCHAR(500) SELECT @sql= 'bcp "SQLCat.dbo.ExcelWorksheetContacts" in "E:\SQLCAT\ExcelWorksheet.xls" -S -PC\SQLSERVER2008R2 -T -c' EXEC xp_cmdshell @sql
EXEC xp_cmdshell 'bcp "Select * from SQLCat.dbo.ExcelWorksheetContacts" queryout "E:\SQLCAT\ExcelWorksheet.xls" -S -PC\SQLSERVER2008R2 -T -c'
DECLARE @sql VARCHAR(500) SELECT @sql= 'bcp "SQLCat.dbo.Text_ConstactsFile" in "E:\SQLCAT\Text_ConstactsFile.txt" -S -PC\SQLSERVER2008R2 -T -c' EXEC xp_cmdshell @sql2. To Export in Text File from SQL Table
EXEC xp_cmdshell 'bcp "SELECT * FROM SQLCat.dbo.Text_ConstactsFile" queryout "E:\SQLCAT\Text_ConstactsFile.txt" -S -PC\SQLSERVER2008R2 -T -c'3. Bulk Insert from Text file to SQL Table
BULK INSERT SQLCat.dbo.Text_ConstactsFile
FROM 'E:\SQLCAT\Text_ConstactsFile.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
CSV File SELECT * INTO SQLCat..CSV_ContactsSheet
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=E:\SQLCAT\;HDR=yes;'
,'SELECT top 2 * FROM CSV_ContactsSheet.CSV'
)
/*-------------------------------- Find the MSDB Tables with Largest Data ------------------------- */
SELECT sc.name + '.' + t.NAME AS TableName,
p.[Rows],
( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, -- Number of total pages * 8KB size of each page in SQL Server
( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,
( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB
FROM msdb.sys.tables t
INNER JOIN msdb.sys.schemas sc ON sc.schema_id = t.schema_id
INNER JOIN msdb.sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN msdb.sys.partitions p ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
INNER JOIN msdb.sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.type_desc = 'USER_TABLE'
AND i.index_id <= 1 --- Heap\ CLUSTERED
GROUP BY sc.name + '.' + t.NAME,
i.[object_id],i.index_id, i.name, p.[Rows]
ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC
/*---------------------------------- Clean "Backup and Restore History" MSDB tables ---------------- */ --- Backup and Restore Tables SELECT * FROM msdb..backupfile SELECT * FROM msdb..backupfilegroup SELECT * FROM msdb..backupmediafamily SELECT * FROM msdb..backupmediaset SELECT * FROM msdb..backupset SELECT * FROM msdb..restorefile SELECT * FROM msdb..restorefilegroup SELECT * FROM msdb..restorehistory --- Execute system stored procedure to reduces the size of backup and restore history tables --- sp_delete_backuphistory @oldest_date EXECUTE msdb..sp_delete_backuphistory '1-OCT-2013' -- Delete entries older than OCT 2013
/*---------------------------------- Clean "Database Mail Log" MSDB tables ------------------------- */ --- Database Mail Log Tables SELECT * FROM msdb..sysmail_faileditems SELECT * FROM msdb..sysmail_unsentitems SELECT * FROM msdb..sysmail_sentitems SELECT * FROM msdb..sysmail_allitems SELECT * FROM msdb..sysmail_log --- Execute system stored procedures to reduces the size of e-mail messages and log tables --1.--e-mail messages-- --- sysmail_delete_mailitems_sp @sent_before , @sent_status EXECUTE msdb..sysmail_delete_mailitems_sp 'October 1, 2013',NULL -- Delete entries older than OCT 2013 --2.--LOG-- --- sysmail_delete_log_sp @logged_before,@event_type EXECUTE msdb..sysmail_delete_log_sp 'October 1, 2013',NULL -- Delete entries older than OCT 2013
/*---------------------------------- Clean "Maintenance Plan Log" MSDB tables ------------------------- */ --- Maintenance Plan Log Tables SELECT * FROM msdb..sysmaintplan_logdetail; SELECT * FROM msdb..sysmaintplan_log; --- Execute system stored procedures to reduces the size maintenance plan tables --Undocumented SP --- sp_maintplan_delete_log @plan_id, @subplan_id, @oldest_time EXECUTE msdb..sp_maintplan_delete_log NULL,NULL,'2013-10-16T00:00:00' -- Delete entries older than OCT 2013 --Alternatively: Truncate Maintenance Plan Log Tables DELETE --SELECT * FROM msdb..sysmaintplan_logdetail; --------------------------- DELETE --SELECT * FROM msdb..sysmaintplan_log;
/*---------------------------------- Release Occupied Spaces ----------------------------------------- */
DBCC SHRINKDATABASE ('MSDB')
