Read Environment Variables in T-SQL

I had recently posted on Use xp_cmdshell results in T_SQL. One of the blog readers Andrew got back asking if it was possible to read Environment Variables using this method and store the value in a variable for later use.

Here’s the query. The variable @windir holds the value and you can use it as you want: (Thanks to my colleague Karthi for simplying the query further)

DECLARE @windir nvarchar(255)
CREATE TABLE #Tmp
(
EnvVar nvarchar(255)
)
INSERT INTO #Tmp exec xp_cmdshell 'echo %windir%'
SET @windir = (SELECT TOP 1 EnvVar from #Tmp)

SELECT @windir as 'Windows Directory'
-- DROP TABLE #Tmp

Note: Observe I am using ‘TOP 1’ in the query since the subquery returned more than one row, one of them being a blank row. I am not sure why is the blank row returned and till I find it out, I will stick with the ‘TOP 1’ command.

OUTPUT

image

Use xp_cmdshell results in T_SQL

We were working on a requirement where we needed to use the result of xp_cmdshell in a T-SQL query.

Note: The users running this commands are members of the sysadmin fixed server.If you want others to be able to execute this command, you will have to explicitly grant other users permission to execute this stored procedure

Here’s how to do store the results of xp_cmdshell for later use. The query shown below returns a directory listing of the current directory and stores it in a Temp table to be used later:

CREATE TABLE #Tmp
(
fileattr nvarchar(255)
)

INSERT INTO #Tmp
EXEC xp_cmdshell 'dir *.exe'

SELECT * FROM #Tmp
--DROP TABLE #Tmp

OUTPUT

image

You can now use a bunch of SUBSTRING commands and extract the date, filesize and filename results, as you want.

Convert Integer to String in SQL Server

A very frequently asked question is how to convert an Integer to String in SQL Server. Here are 3 different ways of doing the same task:

DECLARE @i int
SET @i=98235

--Method 1 : Use CAST function
SELECT CAST(@i as varchar(10))

--Method 2 : Use CONVERT function
SELECT CONVERT(varchar(10),@i)

--Method 3 : Use STR function
SELECT LTRIM(STR(@i,10))

All of them result in the same output

Check if Database Exists In SQL Server – Different ways

A very frequently asked question is how to to check if a Database exists in SQL Server. Here are some different ways.

The following code will be common for all the methods:

DECLARE @db_name varchar(100)
SET @db_name='master'

Method 1: Use sys.sysdatabases view


IF EXISTS(SELECT * FROM sys.sysdatabases where name=@db_name)
PRINT 'The database exists'
else
PRINT 'The database does not exist'

Method 2: Use sysdatabases system table from master database


IF EXISTS(SELECT * FROM master..sysdatabases WHERE name=@db_name)
PRINT 'The database exists'
else
print 'The database does not exist'

Method 3: Using of sp_msforeachdb


--If you dont get a message, the database doesn't exist
DECLARE @sql varchar(1000)
SET @sql='
if ''?''='''+@db_name+''' print ''the database exists'''
EXEC sp_msforeachdb @sql

Method 4: Using sp_msforeachdb with information_schema.schemata


--If you dont get a message, the database doesn't exist
DECLARE @sql varchar(1000)
SET @sql='
if exists(select * from ?.information_schema.schemata where
catalog_name='''+@db_name+''') print ''the database exists'''
EXEC sp_msforeachdb @sql

Display VIEW definition using T-SQL code (3 different ways)

Sometime back, I had posted about 3 Different Ways to display VIEW definition. Let us see some additional ways of displaying VIEW definition using T-SQL and some advantages/disadvantages of using these different methods. Thanks to Madhivanan on his inputs in the original posts.

I am using the AdventureWorks database:

Method 1: sp_helptext

sp_helptext 'Purchasing.vVendor'


Although this method is short, however there is no easy way (cursors required) to use the result of this query in another SQL query.


Method 2: sys.comments


SELECT sysco.text as [View Definition]
FROM sys.syscomments sysco
JOIN sys.objects sysob ON sysco.id = sysob.object_id
JOIN sys.schemas syssh ON sysob.schema_id = syssh.schema_id
WHERE sysob.name = 'vVendor'
AND syssh.name = 'Purchasing';

We get the same results as in sp_helptext, except here we can use the result of this query in another SQL query. The disadvantage here is that this method is lengthy and you have to specify the object name and schema name separately.


Method 3: OBJECT_DEFINITION


SELECT OBJECT_DEFINITION 
(OBJECT_ID(N'Purchasing.vVendor'))
as [View Definition]

This is one of my favorite methods as the result obtained from this method can be used in another query. Moreover the query is shorter.

OUTPUT (Same for all the three methods)

image

Note: It is also very easy to see the definitions of all VIEWS in your database using OBJECT_DEFINITION (as suggested by Madhivanan in the original post).


SELECT object_definition(object_id) as [View Definition]
FROM sys.objects
where type='V'

OUTPUT

image

Manually Update Statistics on a SQL Server Database

Statistics allows SQL Server to tune its physical operations while creating an execution plan. This improves query processing and performance.

Once Statistics have been created, you can manually use the sp_updatestats procedure to refresh the statistics with the latest data. sp_updatedstats updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

USE AdventureWorks
GO
EXEC
sp_updatestats

OUTPUT

image

Update: My colleague and SQL Server expert Govind Kanshi shared a script with me to run a few checks before Updating Statistics. Here’s the script for your reference


-- Find out which tables have auto-update off

SELECT o.name AS [Table], i.name AS [Index Name],
STATS_DATE(i.object_id, i.index_id) AS [Update Statistics date],
s.auto_created AS [Created by QueryProcessor], s.no_recompute
AS [Disabled Auto Update Statistics],
s.user_created AS [Created by user]
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.object_id = i.object_id
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.object_id = s.object_id
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
AND no_recompute = 1
ORDER BY STATS_DATE(i.object_id, i.index_id) ASC;

-- set that property on

ALTER INDEX INDEXA
ON dbo.TABLEX
SET (STATISTICS_NORECOMPUTE = OFF) ;

-- statistics update date – this should tell you which stats have not
-- been updated for long time minus info if no_recomputer is on/off

SELECT o.name AS [Table], i.name AS [Index Name],
STATS_DATE(i.object_id, i.index_id) AS [Update Statistics date],
s.auto_created AS [Created by QueryProcessor], s.no_recompute
AS [Disabled Auto Update Statistics],
s.user_created AS [Created by user]
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.object_id = i.object_id
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.object_id = s.object_id
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.object_id, i.index_id) ASC;

-- now update the stats

UPDATE STATISTICS TableX IndexA


If you want to generate and update statistics across all tables, use sp_createstats

PowerPivot for Microsoft Excel 2010 – RTM (SQL Server 2008 R2)

Microsoft recently released the RTM of Microsoft SQL Server 2008 R2 - PowerPivot for Microsoft Excel 2010.

SQL Server BI users will know that Microsoft PowerPivot for Microsoft Excel 2010 is a data analysis tool that can transform mass quantities of Excel data into meaningful information, with incredible speed.

As quoted in the official download page “Microsoft PowerPivot for Microsoft Excel 2010 provides ground-breaking technology, such as fast manipulation of large data sets (often millions of rows), streamlined integration of data, and the ability to effortlessly share your analysis through Microsoft SharePoint 2010”

You can:

  • Process large data sets (often millions of rows) with about the same performance as processing a few hundred rows by leveraging the PowerPivot in-memory engine and its efficient compression algorithms.
  • Integrate data from a multitude of sources, including corporate databases, spreadsheets, reports, text files, and Internet data feeds.
  • Go beyond standard Excel expressions and use PowerPivot’s Data Analysis Expressions (DAX) language to perform powerful data manipulations. Follow relationships between tables as in a database and define sophisticated calculations using familiar and intuitive expressions.
  • Interactively explore, analyze, and create reports without depending on expert knowledge and specialty training by using native Excel 2010 functionality such as PivotTables, slicers, and other familiar analysis features.

Download Link - PowerPivot for Microsoft Excel 2010

Save SQL Snippets in SQL Server Management Studio (SSMS)

My blog readers would probably know that I post a lot of T_SQL snippets and SQL Server Administration Snippets every now and then. One of my blog readers Kurth asked me if I store them in SSMS (SQL Server Management Studio) , for handy reference.

Thanks for asking this question Kurth. The answer is No, but I would love to store my snippets and here’s how using the SSMS Tools Pack created by SQL Server MVP Mladen Prajdić.

SSMS Tools Pack is an ‘excellent’ add-in for Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2008 R2 and their respective Express versions.

Assuming you have downloaded the Tool Pack, Open SSMS and check the Tool Bar for ‘SSMS Tool’

image

Go to SQL Snippets and feel free to use the snippets that come with it or add/import new ones.

For eg: check the ‘SST’ code snippet which automatically types the following for you

image

Go to the Query window, type SST and press Enter

SELECT  TOP 10 *
FROM

Way Cool and good work Mladen!

View Index MetaData – SQL Server

The sp_helpindex stored procedure gives useful information like name, description and key about the indexes on a table or view. However for retrieving detailed information, you should make use of the sys.indexes object catalog view.

Let us say you want to view the Index MetaData for the table Orders in the Northwind database. Here’s the query for it

SELECT name, type_desc, is_unique, allow_row_locks, allow_page_locks,
is_disabled, fill_factor
FROM sys.indexes
WHERE object_id = OBJECT_ID('Orders')

OUTPUT
image

Find Unused Objects in your SQL Server Database

Here’s a disclaimer - There is no easy way of doing this!

Here are a couple of options that you can use to find unused objects in your database:


1. One option is to run the profiler, capture the results and analyze if any objects are used at all. This should be done for a certain period of time. If any application is connected to the database, run each and every functionality available in front end and, capture and analyze the profiler result.


2. Another method is to rename certain objects that you think are not being used and observe the logs over a period of time and check for any code breaks. This is based on a trial-and-error method.


3. Another alternative is via a query

SELECT source_code,last_execution_time
FROM sys.dm_exec_query_stats as stats
CROSS APPLY (
SELECT text as source_code
FROM sys.dm_exec_sql_text(sql_handle))
AS query_text
ORDER BY last_execution_time desc




unused_objects

This query wont give you a list of unused objects. It gives you a list of used objects which you need to keep track for some time and see if any of the objects are not used. It is like monitoring trace result for sometime to determine if any objects are not used.

Block IP Addresses to SQL Server using a Logon Trigger

We were testing a scenario and wanted to block SQL Server connection through certain IP addresses. Here’s how we solved the requirement using a Logon Trigger

CREATE TRIGGER AllowLocalOnly
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @IP Varchar(500)
SET @IP = EVENTDATA().value
('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(500)')
IF @IP
IN ('172.16.255.11', '172.20.254.1', '172.26.254.12')
BEGIN
Print 'There I caught you ' + @IP
ROLLBACK TRANSACTION
END
END
GO

If the user tries to login through any of these I.P. addresses, the Logon trigger will fire after the authentication phase of logging, but before the user session is actually established, and rollback the transaction. This will restrict Login to SQL Server.

If there is an attempt to login from any of these I.P. Addresses, you can find out by searching the SQL Server error log for something similar to – ‘There I caught you 172.16.255.11 ’. Although I haven’t tried but you can also send an email instead of just printing the error in the error log.

Note: Logon Triggers are available only from SQL Server 2005 SP2 onwards.

AFTER UPDATE Trigger in SQL Server

AFTER triggers can be used to fire after updating a table. It can be used to make decisions based on the values being updated.

Consider the following table:

CREATE TABLE emp(empid varchar(10), salary decimal(12,2))
GO
INSERT INTO emp(empid,salary)
SELECT 'EMP01',20000 union all
SELECT 'EMP02',16700 union all
SELECT 'EMP03',2000 union all
SELECT 'EMP04',2800.45 union all
SELECT 'EMP05',50000
GO

Suppose you don’t want anyone to update the salary of an employee more than 100000. In this case, you can use after update trigger as shown below

CREATE TRIGGER alert_me
ON emp
AFTER UPDATE
AS
IF (SELECT max(salary) from deleted) > 100000
RAISERROR ('The salary exceeds 100000 ', 16, 10)
ROLLBACK
GO

Now see what happens if salary is updated with 150000

UPDATE emp
SET salary=1500000
WHERE empid='EMP01'

The result is

image

Note that if you don’t use rollback, you get an alert but the value will be updated to the table.

Email Alerts when something goes wrong with SQL Server

Here’s how to configure your SQL Server to send alert notifications when something goes wrong.

Step 1: Define an Operator. Open SQL Server Management Studio (SSMS) 2008 > Expand SQL Server Agent Node > Right click on Operators > New Operator > Enter a Name of the operator in the Name section. Provide an E-mail address, Net send address, or Pager address or all the three the operator.

For this example, I am using only the E-mail address as shown below. Click OK.

image

Step 2: Let’s now define a new alert. This alert could be based on an error code or severity, or SQL Server performance or WMI event alert.

Right click the Alerts folder > New Alert > define an alert name > choose ‘SQL Server Event alert’ as the alert type > choose ‘all databases’ and in the severity, choose 011 – to raise an error when a specified database object is not found'

image

Select the Response tab now > check the ‘Notify operators’ checkbox and select Admin from the operator list with the Email option.

image

Click OK and an alert has been set. You should be receiving emails now whenever a query fails because of a missing database object. Make sure you have set up SQL Server Database Mail for the email to work.

Make a T-SQL Query Sleep for a certain amount of time

At times, in test scenarios, you may want to emulate that a transaction or a T-SQL query takes ‘x’ amount of time. Well that is possible (although not with precision) in SQL Server (2005/2008) using the WAITFOR command.

Here’s an example of delaying query execution till a specified time interval has elapsed – let us say 10 seconds

USE Northwind
WAITFOR DELAY '00:00:10';
SELECT LASTNAME, FIRSTNAME FROM Employees;




OUTPUT

image

As you can see, we used the WAITFOR statement with a DELAY option to delay code execution for 10 seconds.

There are a couple of things you should note about the WAITFOR statement

- WAITFOR does not accept date values, only time values are allowed

- In some scenarios, the delay specified by you may not exactly the same as the delay caused by WAITFOR

Display DML Triggers in your SQL Server Database

I had recently posted on how to Display DDL Triggers in your SQL Server Database. David G commented back asking how to view DML Trigger in your database. Here’s how to do it

SELECT name as TriggerName, create_date as CreationDate,
OBJECT_NAME(parent_id) NameOfTableOrView
FROM sys.triggers
WHERE parent_class_desc = 'OBJECT_OR_COLUMN'
ORDER BY name

Observe that the parent_class_desc is set to ‘OBJECT_OR_COLUMN’ since we are locating the DML Triggers in the current schema.

Set the current database to AdventureWorks or any other database that has DML Triggers and you get the following results:

image