http://code.google.com/speed/public-dns/
What is Google Public DNS?
Google Public DNS is a free, global Domain Name System (DNS) resolution service, that you can use as an alternative to your current DNS provider.
To try it out:
Configure your network settings to use the IP addresses 8.8.8.8 and 8.8.4.4 as your DNS servers or Read our configuration instructions.If you decide to try Google Public DNS, your client programs will perform all DNS lookups using Google Public DNS.
Why does DNS matter?
The DNS protocol is an important part of the web's infrastructure, serving as the Internet's phone book: every time you visit a website, your computer performs a DNS lookup. Complex pages often require multiple DNS lookups before they start loading, so your computer may be performing hundreds of lookups a day.
Why should you try Google Public DNS? By using Google Public DNS you can:
Speed up your browsing experience. Improve your security. Get the results you expect with absolutely no redirection.
Or
http://www.opendns.com/ provides the below IP's
208.67.222.222
208.67.202.202
Friday, 4 December 2009
Thursday, 3 December 2009
Email Validation Function
Email Validation Function:
CREATE FUNCTION dbo.IsEmailValid (@Email varchar (100))
RETURNS BIT
AS
BEGIN
/*
One instance of @, multiple dots, atleast one after @
no space
after the last dot minimum 2 chars, max 3 chars
some chars between @ and .
No Special Chars
*/
DECLARE @atpos int, @dotpos int
SET @Email = LTRIM(RTRIM(@Email)) -- remove leading and trailing blanks
IF LEN(@Email) = 0 RETURN(0) -- nothing to validate
SET @atpos = charindex('@',@Email) -- position of first (hopefully only) @
IF @atpos <= 1 OR @atpos = LEN(@Email) RETURN(0) -- @ is neither 1st or last or missing
IF CHARINDEX('@', @email, @atpos+1) > 0 RETURN(0) -- Two @s are illegal
IF CHARINDEX(' ',@Email) > 0 RETURN(0) -- Embedded blanks are illegal
SET @dotpos = CHARINDEX('.',Reverse(@Email)) -- location (from rear) of last dot
IF (@dotpos <> 4) or (LEN(@Email) - @dotpos) < @atpos RETURN (0) -- dot / 2 or 3 char, after @
if (@atpos + @dotpos = len(@email)) Return (0) -- Nothing between @ and .
-- Special Characters Scan
Declare @emaillen int, @currpos int, @currascii int
SET @emaillen = LEN(@email)
SET @currpos = 0
WHILE @emaillen != @currpos
BEGIN
SET @currpos = @currpos + 1
SET @currascii = ASCII(substring(@email, @currpos, 1))
if @currascii <= 44 Return (0)
if @currascii = 47 Return (0)
if @currascii >= 58 and @currascii <= 63 Return (0)
if @currascii >= 91 and @currascii <= 94 Return (0)
if @currascii = 96 Return (0)
if @currascii >= 123 Return (0)
END
RETURN(1) -- Hope this is a valid email
END
CREATE FUNCTION dbo.IsEmailValid (@Email varchar (100))
RETURNS BIT
AS
BEGIN
/*
One instance of @, multiple dots, atleast one after @
no space
after the last dot minimum 2 chars, max 3 chars
some chars between @ and .
No Special Chars
*/
DECLARE @atpos int, @dotpos int
SET @Email = LTRIM(RTRIM(@Email)) -- remove leading and trailing blanks
IF LEN(@Email) = 0 RETURN(0) -- nothing to validate
SET @atpos = charindex('@',@Email) -- position of first (hopefully only) @
IF @atpos <= 1 OR @atpos = LEN(@Email) RETURN(0) -- @ is neither 1st or last or missing
IF CHARINDEX('@', @email, @atpos+1) > 0 RETURN(0) -- Two @s are illegal
IF CHARINDEX(' ',@Email) > 0 RETURN(0) -- Embedded blanks are illegal
SET @dotpos = CHARINDEX('.',Reverse(@Email)) -- location (from rear) of last dot
IF (@dotpos <> 4) or (LEN(@Email) - @dotpos) < @atpos RETURN (0) -- dot / 2 or 3 char, after @
if (@atpos + @dotpos = len(@email)) Return (0) -- Nothing between @ and .
-- Special Characters Scan
Declare @emaillen int, @currpos int, @currascii int
SET @emaillen = LEN(@email)
SET @currpos = 0
WHILE @emaillen != @currpos
BEGIN
SET @currpos = @currpos + 1
SET @currascii = ASCII(substring(@email, @currpos, 1))
if @currascii <= 44 Return (0)
if @currascii = 47 Return (0)
if @currascii >= 58 and @currascii <= 63 Return (0)
if @currascii >= 91 and @currascii <= 94 Return (0)
if @currascii = 96 Return (0)
if @currascii >= 123 Return (0)
END
RETURN(1) -- Hope this is a valid email
END
Database Size Report
Database Size Reporting:
Option 1:
Exec sp_databases
Option 2:
Exec sp_helpdb
Option 3:
This below procedure also gives information about each databases:
create procedure spReportDatabaseSizes as /* Purpose: Stored procedure to give at a glance information of database size,free space, file locations and file sizes */ DECLARE @DBInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100), FileSizeMB INT, LogicalFileName sysname, PhysicalFileName NVARCHAR(520), Status sysname, Updateability sysname, RecoveryMode sysname, FreeSpaceMB INT, FreeSpacePct VARCHAR(7), FreeSpacePages INT, PollDate datetime) DECLARE @command VARCHAR(5000) SELECT @command = 'Use [' + '?' + '] SELECT @@servername as ServerName, ' + '''' + '?' + '''' + ' AS DatabaseName, CAST(sysfiles.size/128.0 AS int) AS FileSize, sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct, GETDATE() as PollDate FROM dbo.sysfiles' INSERT INTO @DBInfo (ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate) EXEC sp_MSForEachDB @command SELECT ServerName, DatabaseName, LogicalFileName, PhysicalFileName, FileSizeMB, FreeSpaceMB, FreeSpacePct, Status, Updateability, RecoveryMode, PollDate as DateInfoGenerated FROM @DBInfo ORDER BY ServerName, DatabaseName
Option 1:
Exec sp_databases
Option 2:
Exec sp_helpdb
Option 3:
This below procedure also gives information about each databases:
create procedure spReportDatabaseSizes as /* Purpose: Stored procedure to give at a glance information of database size,free space, file locations and file sizes */ DECLARE @DBInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100), FileSizeMB INT, LogicalFileName sysname, PhysicalFileName NVARCHAR(520), Status sysname, Updateability sysname, RecoveryMode sysname, FreeSpaceMB INT, FreeSpacePct VARCHAR(7), FreeSpacePages INT, PollDate datetime) DECLARE @command VARCHAR(5000) SELECT @command = 'Use [' + '?' + '] SELECT @@servername as ServerName, ' + '''' + '?' + '''' + ' AS DatabaseName, CAST(sysfiles.size/128.0 AS int) AS FileSize, sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct, GETDATE() as PollDate FROM dbo.sysfiles' INSERT INTO @DBInfo (ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate) EXEC sp_MSForEachDB @command SELECT ServerName, DatabaseName, LogicalFileName, PhysicalFileName, FileSizeMB, FreeSpaceMB, FreeSpacePct, Status, Updateability, RecoveryMode, PollDate as DateInfoGenerated FROM @DBInfo ORDER BY ServerName, DatabaseName
How to Find Worst SQL Queries by Dynamic Management View
Worst SQL Queries:
Eg: exec usp_Worst_TSQL @orderby='TCPU'
CREATE PROC [dbo].[usp_Worst_TSQL]
/*
Name: usp_Worst_TSQL
Description: This stored procedure displays the top worst performing queries based on CPU, Execution Count,
I/O and Elapsed_Time as identified using DMV information. This can be display the worst
performing queries from an instance, or database perspective. The number of records shown,
the database, and the sort order are identified by passing pararmeters.
Parameters: There are three different parameters that can be passed to this procedures: @DBNAME, @COUNT
and @ORDERBY. The @DBNAME is used to constraint the output to a specific database. If
when calling this SP this parameter is set to a specific database name then only statements
that are associated with that database will be displayed. If the @DBNAME parameter is not set
then this SP will return rows associated with any database. The @COUNT parameter allows you
to control the number of rows returned by this SP. If this parameter is used then only the
TOP x rows, where x is equal to @COUNT will be returned, based on the @ORDERBY parameter.
The @ORDERBY parameter identifies the sort order of the rows returned in descending order.
This @ORDERBY parameters supports the following type: CPU, AE, TE, EC or AIO, TIO, ALR, TLR, ALW, TLW, APR, and TPR
where "ACPU" represents Average CPU Usage
"TCPU" represents Total CPU usage
"AE" represents Average Elapsed Time
"TE" represents Total Elapsed Time
"EC" represents Execution Count
"AIO" represents Average IOs
"TIO" represents Total IOs
"ALR" represents Average Logical Reads
"TLR" represents Total Logical Reads
"ALW" represents Average Logical Writes
"TLW" represents Total Logical Writes
"APR" represents Average Physical Reads
"TPR" represents Total Physical Read
Typical execution calls
Top 6 statements in the AdventureWorks database base on Average CPU Usage:
EXEC usp_Worst_TSQL @DBNAME='AdventureWorks',@COUNT=6,@ORDERBY='ACPU';
Top 100 statements order by Average IO
EXEC usp_Worst_TSQL @COUNT=100,@ORDERBY='ALR';
Show top all statements by Average IO
EXEC usp_Worst_TSQL;
*/
(@DBNAME VARCHAR(128) = ''
,@COUNT INT = 999999999
,@ORDERBY VARCHAR(4) = 'AIO')
AS
-- Check for valid @ORDERBY parameter
IF ((SELECT CASE WHEN
@ORDERBY in ('ACPU','TCPU','AE','TE','EC','AIO','TIO','ALR','TLR','ALW','TLW','APR','TPR')
THEN 1 ELSE 0 END) = 0)
BEGIN
-- abort if invalid @ORDERBY parameter entered
RAISERROR('@ORDERBY parameter not APCU, TCPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR or TPR',11,1)
RETURN
END
SELECT TOP (@COUNT)
COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [Database Name]
-- find the offset of the actual statement being executed
,SUBSTRING(text,
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS [Statement]
,OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema Name]
,OBJECT_NAME(st.objectid,dbid) [Object Name]
,objtype [Cached Plan objtype]
,execution_count [Execution Count]
,(total_logical_reads + total_logical_writes + total_physical_reads )/execution_count [Average IOs]
,total_logical_reads + total_logical_writes + total_physical_reads [Total IOs]
,total_logical_reads/execution_count [Avg Logical Reads]
,total_logical_reads [Total Logical Reads]
,total_logical_writes/execution_count [Avg Logical Writes]
,total_logical_writes [Total Logical Writes]
,total_physical_reads/execution_count [Avg Physical Reads]
,total_physical_reads [Total Physical Reads]
,total_worker_time / execution_count [Avg CPU]
,total_worker_time [Total CPU]
,total_elapsed_time / execution_count [Avg Elapsed Time]
,total_elapsed_time [Total Elasped Time]
,last_execution_time [Last Execution Time]
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE attribute = 'dbid' AND
CASE when @DBNAME = '' THEN ''
ELSE COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT)) + '*',
'Resource') END
IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + '*')
ORDER BY CASE
WHEN @ORDERBY = 'ACPU' THEN total_worker_time / execution_count
WHEN @ORDERBY = 'TCPU' THEN total_worker_time
WHEN @ORDERBY = 'AE' THEN total_elapsed_time / execution_count
WHEN @ORDERBY = 'TE' THEN total_elapsed_time
WHEN @ORDERBY = 'EC' THEN execution_count
WHEN @ORDERBY = 'AIO' THEN (total_logical_reads + total_logical_writes + total_physical_reads) / execution_count
WHEN @ORDERBY = 'TIO' THEN total_logical_reads + total_logical_writes + total_physical_reads
WHEN @ORDERBY = 'ALR' THEN total_logical_reads / execution_count
WHEN @ORDERBY = 'TLR' THEN total_logical_reads
WHEN @ORDERBY = 'ALW' THEN total_logical_writes / execution_count
WHEN @ORDERBY = 'TLW' THEN total_logical_writes
WHEN @ORDERBY = 'APR' THEN total_physical_reads / execution_count
WHEN @ORDERBY = 'TPR' THEN total_physical_reads
END DESC
Eg: exec usp_Worst_TSQL @orderby='TCPU'
CREATE PROC [dbo].[usp_Worst_TSQL]
/*
Name: usp_Worst_TSQL
Description: This stored procedure displays the top worst performing queries based on CPU, Execution Count,
I/O and Elapsed_Time as identified using DMV information. This can be display the worst
performing queries from an instance, or database perspective. The number of records shown,
the database, and the sort order are identified by passing pararmeters.
Parameters: There are three different parameters that can be passed to this procedures: @DBNAME, @COUNT
and @ORDERBY. The @DBNAME is used to constraint the output to a specific database. If
when calling this SP this parameter is set to a specific database name then only statements
that are associated with that database will be displayed. If the @DBNAME parameter is not set
then this SP will return rows associated with any database. The @COUNT parameter allows you
to control the number of rows returned by this SP. If this parameter is used then only the
TOP x rows, where x is equal to @COUNT will be returned, based on the @ORDERBY parameter.
The @ORDERBY parameter identifies the sort order of the rows returned in descending order.
This @ORDERBY parameters supports the following type: CPU, AE, TE, EC or AIO, TIO, ALR, TLR, ALW, TLW, APR, and TPR
where "ACPU" represents Average CPU Usage
"TCPU" represents Total CPU usage
"AE" represents Average Elapsed Time
"TE" represents Total Elapsed Time
"EC" represents Execution Count
"AIO" represents Average IOs
"TIO" represents Total IOs
"ALR" represents Average Logical Reads
"TLR" represents Total Logical Reads
"ALW" represents Average Logical Writes
"TLW" represents Total Logical Writes
"APR" represents Average Physical Reads
"TPR" represents Total Physical Read
Typical execution calls
Top 6 statements in the AdventureWorks database base on Average CPU Usage:
EXEC usp_Worst_TSQL @DBNAME='AdventureWorks',@COUNT=6,@ORDERBY='ACPU';
Top 100 statements order by Average IO
EXEC usp_Worst_TSQL @COUNT=100,@ORDERBY='ALR';
Show top all statements by Average IO
EXEC usp_Worst_TSQL;
*/
(@DBNAME VARCHAR(128) = '
,@COUNT INT = 999999999
,@ORDERBY VARCHAR(4) = 'AIO')
AS
-- Check for valid @ORDERBY parameter
IF ((SELECT CASE WHEN
@ORDERBY in ('ACPU','TCPU','AE','TE','EC','AIO','TIO','ALR','TLR','ALW','TLW','APR','TPR')
THEN 1 ELSE 0 END) = 0)
BEGIN
-- abort if invalid @ORDERBY parameter entered
RAISERROR('@ORDERBY parameter not APCU, TCPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR or TPR',11,1)
RETURN
END
SELECT TOP (@COUNT)
COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [Database Name]
-- find the offset of the actual statement being executed
,SUBSTRING(text,
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS [Statement]
,OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema Name]
,OBJECT_NAME(st.objectid,dbid) [Object Name]
,objtype [Cached Plan objtype]
,execution_count [Execution Count]
,(total_logical_reads + total_logical_writes + total_physical_reads )/execution_count [Average IOs]
,total_logical_reads + total_logical_writes + total_physical_reads [Total IOs]
,total_logical_reads/execution_count [Avg Logical Reads]
,total_logical_reads [Total Logical Reads]
,total_logical_writes/execution_count [Avg Logical Writes]
,total_logical_writes [Total Logical Writes]
,total_physical_reads/execution_count [Avg Physical Reads]
,total_physical_reads [Total Physical Reads]
,total_worker_time / execution_count [Avg CPU]
,total_worker_time [Total CPU]
,total_elapsed_time / execution_count [Avg Elapsed Time]
,total_elapsed_time [Total Elasped Time]
,last_execution_time [Last Execution Time]
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE attribute = 'dbid' AND
CASE when @DBNAME = '
ELSE COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT)) + '*',
'Resource') END
IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + '*')
ORDER BY CASE
WHEN @ORDERBY = 'ACPU' THEN total_worker_time / execution_count
WHEN @ORDERBY = 'TCPU' THEN total_worker_time
WHEN @ORDERBY = 'AE' THEN total_elapsed_time / execution_count
WHEN @ORDERBY = 'TE' THEN total_elapsed_time
WHEN @ORDERBY = 'EC' THEN execution_count
WHEN @ORDERBY = 'AIO' THEN (total_logical_reads + total_logical_writes + total_physical_reads) / execution_count
WHEN @ORDERBY = 'TIO' THEN total_logical_reads + total_logical_writes + total_physical_reads
WHEN @ORDERBY = 'ALR' THEN total_logical_reads / execution_count
WHEN @ORDERBY = 'TLR' THEN total_logical_reads
WHEN @ORDERBY = 'ALW' THEN total_logical_writes / execution_count
WHEN @ORDERBY = 'TLW' THEN total_logical_writes
WHEN @ORDERBY = 'APR' THEN total_physical_reads / execution_count
WHEN @ORDERBY = 'TPR' THEN total_physical_reads
END DESC
Top 100 Queries from particular instance of server
Top 100 Queries from particular instance of server:
create procedure usp_top100_queries as select top 100 a.total_worker_time,b.text from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(a.plan_handle) as b order by 1 desc
create procedure usp_top100_queries as select top 100 a.total_worker_time,b.text from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(a.plan_handle) as b order by 1 desc
Replication Troubleshooting Information
Replication Troubleshoot Information Table:
http://vyaskn.tripod.com/repl_ans.htm
use distribution
go
select * from MSlogreader_history order by agent_id desc
select * from MSsnapshot_history order by agent_id desc
select * from MSdistribution_history order by agent_id desc
select * from MSmerge_history
XML File Read through OPENROWSET
OBJECTIVE: To read a xml file in sql server 2005
FILE CONTENT STARTED:
-2009-11-25 04:26:00 - 18B15GD022 Mobile Broadband ZTE MF627 USB Modem Broadband (15GB) - 18 months contract from 3 Mobile Mobile Phone Contract n/a n/a ZTE MF627 USB Modem Mobile Phones Contract 372 Broadband (15GB) - 15 GB of data allowance every month. Get Broadband Anywhere with Speeds upto 3.6Mbps http://3mobileshop.at/adsvine/18B15GD022.html http://3mobileshop.at/adsvine?CTY=9&DURL=http://threestore.three.co.uk/DealSummary.aspx?tariffid=1239&offerCode=18B15GD022&id=1183 http://threestore.three.co.uk/images/mixnmatch/ZTE-modem(38x80).jpg GBP 1 in stock Broadband (15GB) - With 0 included minutes and 0 texts; 18 months mobile broadband contract from 3 Mobile. Plus Contract Length; Get Broadband Anywhere with Speeds upto 3.6Mbps Broadband (15GB) - 15 GB of data allowance every month. Get Broadband Anywh ZTE MF627 USB Modem 0.00 0 20.00 3 Broadband (15GB)
FILE CONTENT FINISHED:
DECLARE @xml as XML
SELECT @xml=CONVERT(xml, BulkColumn, 2)
FROM
OPENROWSET(Bulk 'D:\DailyScripts\xml\Mobiles.xml', SINGLE_BLOB) [rowsetresults]
SELECT
cast(T.Item.query('../last_updated/text()') as varchar(20)) lastUpdate,
cast(T.Item.query('product_code/text()') as varchar(20)) Product_Code ,
cast(T.Item.query('product_name/text()') as nvarchar(100)) Product_Name
FROM @xml.nodes('/products/product') AS T(Item)
FILE CONTENT STARTED:
-
FILE CONTENT FINISHED:
DECLARE @xml as XML
SELECT @xml=CONVERT(xml, BulkColumn, 2)
FROM
OPENROWSET(Bulk 'D:\DailyScripts\xml\Mobiles.xml', SINGLE_BLOB) [rowsetresults]
SELECT
cast(T.Item.query('../last_updated/text()') as varchar(20)) lastUpdate,
cast(T.Item.query('product_code/text()') as varchar(20)) Product_Code ,
cast(T.Item.query('product_name/text()') as nvarchar(100)) Product_Name
FROM @xml.nodes('/products/product') AS T(Item)
Table Size Information
Objecive: To find table size information for particular database
USE DBNAME
go
SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
DESCRIPTION
Returns TABLE Size Information
SORTING USAGE
@Sort bit VALUES
0 = Alphabetically BY TABLE name
1 = Sorted BY total space used by TABLE
*/
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 1 /* Edit this value FOR sorting options */
/* DO NOT EDIT ANY CODE BELOW THIS LINE */
--- =--Create temporary table
CREATE TABLE #TempTable
( [Table_Name] varchar(150),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
--- =--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary table
INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @Sort = 0
BEGIN
--Retrieve Table Data and Sort Alphabetically
SELECT * FROM #TempTable ORDER BY Table_Name
END
ELSE
BEGIN
/*Retrieve TABLE Data AND Sort BY the size OF the Table*/
SELECT *, cast(replace(table_size, 'kb', '') as int) ts FROM #TempTable ORDER BY ts DESC
END
DROP TABLE #TempTable
USE DBNAME
go
SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
DESCRIPTION
Returns TABLE Size Information
SORTING USAGE
@Sort bit VALUES
0 = Alphabetically BY TABLE name
1 = Sorted BY total space used by TABLE
*/
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 1 /* Edit this value FOR sorting options */
/* DO NOT EDIT ANY CODE BELOW THIS LINE */
--- =--Create temporary table
CREATE TABLE #TempTable
( [Table_Name] varchar(150),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
--- =--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary table
INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @Sort = 0
BEGIN
--Retrieve Table Data and Sort Alphabetically
SELECT * FROM #TempTable ORDER BY Table_Name
END
ELSE
BEGIN
/*Retrieve TABLE Data AND Sort BY the size OF the Table*/
SELECT *, cast(replace(table_size, 'kb', '') as int) ts FROM #TempTable ORDER BY ts DESC
END
DROP TABLE #TempTable
Subscribe to:
Comments (Atom)