How to import csv file with OPENROWSET?
Execute the following script to demonstrate data import from .csv flat file using OPENROWSET applying 2 different providers.
USE Master
Go
EXEC sp_configure ‘filestream access level’, 2
Go
RECONFIGURE
Go
————————————————————————————————————————————————————–
How to import Excel data with OPENROWSET?
Execute the following script to demonstrate the alternative ways of importing Excel worksheet data with OPENROWSET.
How to list all table row counts in a database?
Execute the following script to demonstrate how to obtain row counts for all tables in a database.
Execute the following script to demonstrate data import from .csv flat file using OPENROWSET applying 2 different providers.
USE AdventureWorks2008;
EXEC sp_configure
GO
– [Ad Hoc Distributed Queries] run_value should be 1
SELECT * FROM OPENROWSET(‘MSDASQL’,
‘Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=F:\data\export\csv\;’,
‘SELECT * FROM Top10.csv’)
GO
–Using a different provider
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Text;Database=F:\data\export\csv\;HDR=YES’,
‘SELECT * FROM Top10.csv’)
GO
—————————————————–
How to list all table sizes in a database?
Execute the following script to list the database and each table size within the AdventureWorks2008 database. If you need to store size information for long-term tracking of database growth, permanent table can be used instead of @tablevariable.use AdventureWorks2008;
go
– Space used by AdventureWorks2008 database
exec sp_spaceused
go
/* Results
database_name database_size unallocated space
AdventureWorks2008 182.06 MB 0.00 MB
reserved data index_size unused
184608 KB 96112 KB 82064 KB 6432 KB
*/
– Space used for all tables
declare @TableSpace table (TableName sysname, RowsK varchar(32),
ReservedMB varchar(32), DataMB varchar(32),
IndexSizeMB varchar(32), UnusedMB varchar(32))
insert @TableSpace
exec sp_MSforeachtable @command1=“exec sp_spaceused ‘?’;”
update @TableSpace set RowsK = CONVERT(varchar,
1+convert(int, RowsK)/1024)
update @TableSpace set ReservedMB = CONVERT(varchar,
1+convert(int,LEFT(ReservedMB, charindex(‘ K’, ReservedMB,-1)))/1024)
update @TableSpace set DataMB = CONVERT(varchar,
1+convert(int,LEFT(DataMB, charindex(‘ K’, DataMB,-1)))/1024)
update @TableSpace set IndexSizeMB = CONVERT(varchar,
convert(int,LEFT(IndexSizeMB, charindex(‘ K’, IndexSizeMB,-1)))/1024)
update @TableSpace set UnusedMB = CONVERT(varchar,
convert(int,LEFT(UnusedMB, charindex(‘ K’, UnusedMB,-1)))/1024)
select * from @TableSpace order by convert(int,DataMB) desc
go
Partial results:
| TableName | RowsK | ReservedMB | DataMB | IndexSizeMB | UnusedMB |
| Person | 20 | 82 | 30 | 51 | 0 |
| SalesOrderDetail | 119 | 16 | 10 | 5 | 0 |
| DatabaseLog | 2 | 7 | 7 | 0 | 0 |
| TransactionHistory | 111 | 10 | 7 | 3 | 0 |
———————————-
How to search all sprocs and views?
Execute the following script to demonstrate searching all sprocs and views for keywords such as table name, column name, database name, etc..use AdventureWorks2008;
– Search all stored procedures
select StoredProcedureName
=convert(sysname,SCHEMA_NAME(schema_id))+‘.’+name
from sys.objects where type = ‘P’
and PATINDEX (‘%HumanResources%’, OBJECT_DEFINITION (object_id)) > 0
go
– Search all views
select ViewName
=convert(sysname,SCHEMA_NAME(schema_id))+‘.’+name
from sys.objects where type = ‘V’
and PATINDEX (‘%Store%’, OBJECT_DEFINITION (object_id)) > 0
go
——————
How to list disk drives with total and free space?
Execute the following script to create a table-valued function and a stored procedure to list all drives with total disk space and free disk space. Note that the table-valued function “join”-ed with a CROSS APPLY in the sproc to obtain the final results.USE AdventureWorks2008;
GO
/*
– Turn on OLE automation if not on
exec sp_configure ‘show advanced options’, 1
go
RECONFIGURE
GO
exec sp_configure ‘Ole Automation Procedures’, 1
GO
RECONFIGURE
GO
*/
CREATE FUNCTION fnTotalDriveSpace
(@DriveLetter CHAR(1))
RETURNS @Total TABLE (MaxSpaceGB money)
BEGIN
DECLARE @return INT, @fso INT, @GetDrive VARCHAR(16)
DECLARE @drv INT, @DriveSizeinBytes VARCHAR(32)
SET @GetDrive = ‘GetDrive(“‘ + @DriveLetter + ‘”)’
EXEC @return = sp_OACreate ‘Scripting.FileSystemObject’, @fso OUTPUT
SET @DriveSizeinBytes = NULL
IF @return = 0
EXEC @return = sp_OAMethod @fso, @GetDrive, @drv OUTPUT
IF @return = 0
EXEC @return = sp_OAGetProperty @drv,‘TotalSize’, @DriveSizeinBytes OUTPUT
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso
INSERT @Total values (
(((convert(bigint,@DriveSizeinBytes)/ 1024)/ 1024)/1024) )
RETURN
END
GO
– select * from dbo.fnTotalDriveSpace(‘C’)
CREATE PROC sprocDriveSpaceInfo
AS
BEGIN
DECLARE @Drives TABLE ( DriveLetter char(1), FreeGB money)
INSERT @Drives (DriveLetter, FreeGB)
EXEC xp_fixeddrives
UPDATE @Drives SET FreeGB = Floor(FreeGB/1024)
SELECT
DriveLetter,
FreeGB=convert(int,FreeGB),
MaxSpaceGB=convert(int,MaxSpaceGB)
FROM @Drives d
CROSS APPLY dbo.fnTotalDriveSpace (d.DriveLetter)
ORDER BY DriveLetter
END
GO
EXEC sprocDriveSpaceInfo
GO
Results:
| DriveLetter | FreeGB | MaxSpaceGB |
| C | 316 | 688 |
| D | 1 | 9 |
——————————————————————————————————–
How to import flat file into staging table with numbering?
Execute the following script to create a staging table and import (BULK INSERT) a flat file into it numbering each row with the identity(1,1) sequence.USE AdventureWorks2008;
CREATE TABLE #stgImportFlatfile (
Record varchar(256) null)
GO
CREATE TABLE stgImportFlatfile (
ID int identity(1,1) primary key,
Record varchar(256) null)
GO
BULK INSERT #stgImportFlatfile
FROM ‘F:\data\import\ProductBulk.txt’
GO
DELETE #stgImportFlatfile WHERE Record is null
GO
INSERT stgImportFlatfile(Record)
SELECT * FROM #stgImportFlatfile
GO
SELECT * from stgImportFlatfile
GO
DROP TABLE #stgImportFlatfile
GO
– DROP TABLE stgImportFlatfile
GO
————————————————————————
How to list all indexes in a database?
Execute the following script to all indexes in AdventureWorks database sorted by IndexType. XML indexes included, full-text indexes not included.USE AdventureWorks2008;
SELECT
SchemaName = schema_name(schema_id),
TableName = object_name(o.object_id),
IndexName = i.Name,
IndexType = i.type_desc,
IsUnique = case when is_unique=1 then ‘UNIQUE’ else ” end,
IsPrimaryKey = case when is_primary_key=1 then ‘PRIMARY KEY’ else ” end,
[FillFactor] = i.fill_factor
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE i.Name is not null
and o.type = ‘U’
ORDER BY IndexType, SchemaName, TableName, IndexName
—————————————————————
How to configure FILESTREAM on SQL Server 2008?
Execute the following script to turn on the FILESTREAM facility on the current server instance. You can also use Object Explorer GUI to configure it. The FILESTREAM feature, new to SQL Server 2008, provides a way to store and access large ( > 2GB) binary values, either through SQL Server or by direct access to the Windows NTFS file system. It can be used for smaller varbinary(max) storage as well.USE Master
Go
EXEC sp_configure ‘filestream access level’, 2
Go
RECONFIGURE
Go
————————————————————————————————————————————————————–
How to import Excel data with OPENROWSET?
Execute the following script to demonstrate the alternative ways of importing Excel worksheet data with OPENROWSET.
SELECT *
INTO #SpreadSheet1
FROM
OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′
,‘Excel 8.0;Database=D:\data\excel\generalledger.xls’
,‘SELECT * FROM [Sheet1$]‘
)
GO
SELECT *
INTO #SpreadSheet2
FROM OPENROWSET(‘MSDASQL’,
‘Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\data\excel\generalledger.xls’,
‘SELECT * FROM [Sheet1$]‘)
GO
SELECT * FROM #SpreadSheet1
GO
SELECT * FROM #SpreadSheet2
GO
——————————————————————————–
How to test linked server connectivity?
Execute the following script to create a stored procedure to perform a ping test for the connectivity of a linked server:USE AdventureWorks2008;
GO
CREATE PROC procPingLinkedServer @LinkedServer sysname
/* RETURNS
1 –> Connection success
0 –> Connection failure
*/
AS
BEGIN
DECLARE @Command nvarchar(1048)
SET NOCOUNT ON;
CREATE TABLE #PingTest ( CmdResultBuffer varchar(128));
SET @Command = ‘ping ‘+ @LinkedServer
PRINT @Command
INSERT #PingTest
EXEC master..xp_cmdshell @Command;
IF EXISTS ( SELECT 1 FROM #PingTest WHERE CmdResultBuffer LIKE ‘%TTL%’ )
RETURN 1;
ELSE
RETURN 0;
drop table #PingTest
END
GO
– Test linked server connectivity
DECLARE @Connection int
EXEC @Connection = procPingLinkServer ‘LINKEDSERVERALPHA’
SELECT ConnectionStatus = @Connection
———————————————————————————
How to list connection information for each session?
Execute the following script to demonstrate the listing of connection information for each session_id. The CROSS APPLY operator is used to obtain the last SQL query text for the session.USE AdventureWorks2008;
SELECT
ec.session_id,
st.text as SQLText,
ec.most_recent_session_id,
ec.connect_time,
ec.last_read,
ec.last_write,
ec.num_reads,
ec.num_writes,
ec.net_transport,
ec.encrypt_option,
ec.auth_scheme,
ec.protocol_type,
ec.protocol_version,
ec.net_packet_size,
ec.endpoint_id,
ec.client_net_address,
ec.client_tcp_port,
ec.local_net_address,
ec.local_tcp_port,
ec.node_affinity,
ec.parent_connection_id,
CASE WHEN st.dbid = 32767 THEN ‘Resource DB’
ELSE coalesce(db_name(st.dbid),”) END as DBName,
CASE WHEN st.dbid IS NULL THEN ”
ELSE object_schema_name(st.objectid, st.dbid) END as [Schema],
CASE WHEN st.dbid IS NULL THEN ”
ELSE object_name(st.objectid, st.dbid) END as [Object]
FROM sys.dm_exec_connections ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
———————————————————————–
How to list all column defaults in a database?
Execute the following script to demonstrate the listing of all defaults for columns in AdventureWorks2008 database.use AdventureWorks2008;
select
TableName=object_name(parent_object_id),
TableColumn = c.name,
*
from sys.objects o
join sys.columns c
on o.object_id = c.default_object_id
where o.type=‘D’
order by TableName, c.column_id
go
——————————————————————————
How to disable all constraints and all triggers?
Execute the following script to demonstrate the disabling of all constraints and all triggers in the AdventureWorks2008 sample database.USE AdventureWorks2008;
DECLARE @SQLCommand VARCHAR(MAX)
SET @SQLCommand = ”
SELECT @SQLCommand = ISNULL(@SQLCommand,”) + ‘ALTER TABLE ‘ +
QUOTENAME(s.name) + ‘.’ + QUOTENAME(o.name) +
‘ NOCHECK CONSTRAINT ALL;’ + CHAR(13)
FROM sys.objects o
JOIN sys.schemas s
on o.schema_id = s.schema_id
WHERE type = ‘U’
PRINT @SQLCommand
EXEC(@SQLCommand)
SET @SQLCommand = ”
SELECT @SQLCommand = ISNULL(@SQLCommand,”) + ‘ALTER TABLE ‘ +
QUOTENAME(s.name) + ‘.’ + QUOTENAME(o.name) +
‘ DISABLE TRIGGER ALL;’ + CHAR(13)
FROM sys.objects o
JOIN sys.schemas s
on o.schema_id = s.schema_id
WHERE type = ‘U’
PRINT @SQLCommand
EXEC(@SQLCommand)
——————————————-
How to apply RESTORE checks on database backup files?
Execute the following script to demonstrate the restore command variations for checking the content of a database backup file:USE master;
restore headeronly from disk=‘F:\data\backup\inventorydb.bak’
restore filelistonly from disk=‘F:\data\backup\inventorydb.bak’
restore labelonly from disk=‘F:\data\backup\inventorydb.bak’
restore verifyonly from disk=‘F:\data\backup\inventorydb.bak’
————————————————————
How to put the SSIS Import/Export Wizard on Your Desktop?
Execute the following step to create a shortcut on the desktop or quick launch toolbar for the SSIS Import/Export Wizard:- Open the folder where SSIS is installed. The default location is: C:\Program Files\Microsoft SQL Server\100\DTS\Binn
- Right click on DTSWizard and select shortcut (finished, it appears on desktop), or drag/drop it to the task bar in the quick launch toolbar
- When the context menu pops up, select Create a shortcut here
- Click on name below desktop icon, rename to “SSIS 2008 Wizard”
How to list all table row counts in a database?
Execute the following script to demonstrate how to obtain row counts for all tables in a database.
use AdventureWorks2008;
go
create table #RowCount (TableName sysname, NoOfRows int)
exec sp_MSforeachtable
‘insert into #RowCount select ”?”, count(*) from ?’
select * from #RowCount
order by NoOfRows desc
drop table #RowCount
go
—————————————————————————–
How to check execution frequency for sprocs and views?
Execute the following script to build a stored procedure to count the execution frequency of stored procedures and views in memory cache. Rebooting the server will clear the counts.USE AdventureWorks2008;
GO
CREATE PROCEDURE procExecutionFrequency @Database sysname
AS
BEGIN
SELECT
cp.objtype ‘ObjectType’,
min(DB_NAME(st.dbid)) +‘.’
+OBJECT_SCHEMA_NAME(st.objectid,dbid) +‘.’
+OBJECT_NAME(st.objectid,dbid) ‘ObjectName’
,max(cp.usecounts) ‘ExecutionFrequency’
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE
cp.objtype in ( ‘proc’, ‘view’)
AND DB_NAME(st.dbid) = @Database
GROUP BY
cp.objtype,
cp.plan_handle,
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
ORDER BY ObjectType, ExecutionFrequency desc
END
GO
EXEC procExecutionFrequency ‘AdventureWorks2008′
—————————————————————————–
How to backup database with datetimestamp in file name?
Execute the following script to demonstrate the backup of AdventureWorks2008 database with the datetimestamp in the backup file name.USE master;
DECLARE @FileName NVARCHAR(1024)
SET @FileName = ‘F:\data\AdventureWorks2008\backup\AW2008′+REPLACE(REPLACE(CONVERT(varchar(100), GETDATE(), 100),‘:’,‘_’),‘ ‘,‘_’)+‘.BAK’
SET @FileName = REPLACE(@FileName,‘__’,‘_’)
PRINT @FileName
BACKUP DATABASE AdventureWorks2008 TO DISK = @FileName WITH NOFORMAT, INIT, NAME = N’AdventureWorks2008-Full Database Backup’
GO
————————————————————————-
How to list all tables with columns in a database?
Execute the following script to demonstrate the listing of all tables with column information in a database.USE AdventureWorks;
SELECT [Table] = s0.name + ‘.’ + o.name,
[Column]=c.name,
[ColumnType] = t.name,
[Length] = c.max_length
FROM sys.schemas s0
JOIN sys.objects o
ON o.schema_id = s0.schema_id
JOIN sys.columns c
ON o.object_id = c.object_id
LEFT JOIN (sys.types t
JOIN sys.schemas s1
ON t.schema_id = s1.schema_id)
ON c.user_type_id = t.user_type_id
WHERE o.type = ‘U’
ORDER BY s0.Name, o.Name
GO
———————————————————————-
How to mark a stored procedure for automatic startup execution?
Execute the following script to demonstrate the configuration of the stored procedure “ServerInitSprocs” for startup execution. The sp_procoption system stored procedure marks the application stored procedure for automatic execution when SQL Server starts or rebooted. Only objects in the master database owned by dbo can have the startup execution and the option is restricted to sprocs that have no parameters.USE master
EXEC sp_procoption ‘ServerInitSprocs’, ‘startup’, ‘true’
GO
———————————————————————
No comments:
Post a Comment