Archive

Posts Tagged ‘sql server 2012’

SQL Server – Different Ways to Check Object Definition

14.04.2014 2 comments

sp_helptext is widely used for checking object definition in SQL Server. sp_helptext can be used to check definition of various database objects like Views, Stored Procedures and User Defined Functions.

There are two other options which can be used to retrieve object definition:

OBJECT_DEFINITION( object_id ) – is a built-in function. It can also retrieve definitions of CHECK/DEFAULT constraints

sys.sql_modules – is a catalog view which returns definitions of all modules in current database

Each of these can be used as follows:


USE [SqlAndMe]
GO

sp_helptext 'MyProcedure'
GO

-- Use OBJECT_ID() function to get object id
SELECT    OBJECT_DEFINITION(OBJECT_ID('MyProcedure'))
GO

-- Use OBJECT_ID() function to get object id
SELECT    [definition]
FROM    sys.sql_modules
WHERE    object_id = OBJECT_ID('MyProcedure')
GO

OBJECT_DEFINITION(object_id) and sys.sql_modules returns results as a single-line when in “Results to Grid” (Ctrl + D) mode. Switch to “Results to Text” (Ctrl + T) for formatted output which will include line breaks.

Hope This Helps!

Vishal

SQL Server – How to get last access/update time for a table

11.03.2014 2 comments

Modify date and create date for a table can be retrieved from sys.tables catalog view. When any structural changes are made the modify date is updated. It can be queried as follows:


USE [SqlAndMe]
GO

SELECT    [TableName] = name,
create_date,
modify_date
FROM    sys.tables
WHERE    name = 'TransactionHistoryArchive'
GO

 

 

sys.tables only shows modify date for structural changes. If we need to check when was the tables last updated or accessed, we can use dynamic management view sys.dm_db_index_usage_stats. This DMV returns counts of different types of index operations and last time the operation was performed.

It can be used as follows:


USE [SqlAndMe]
GO

SELECT    [TableName] = OBJECT_NAME(object_id),
last_user_update, last_user_seek, last_user_scan, last_user_lookup
FROM    sys.dm_db_index_usage_stats
WHERE    database_id = DB_ID('SqlAndMe')
AND        OBJECT_NAME(object_id) = 'TransactionHistoryArchive'
GO

 

 

last_user_update – provides time of last user update

last_user_* – provides time of last scan/seek/lookup

It is important to note that sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted.

 

Hope This Helps!

Vishal

SQL Server – Filtering objects in Object Explorer – Management Studio

28.05.2013 3 comments

When you are working on a database with a large number of objects sometimes it becomes a challenge to locate objects. Object Explorer in SQL Server Management Studio lists all objects according to category and it may be hard to locate specific object.

To resolve this you can Object Explorer provides filtering for objects. You can apply a filter in Object Explorer for common object categories. i.e. Table, Views, Stored Procedures etc.

For example, if we need to locate a stored procedure which has "Sales" in name we can use Object Explorer filtering as below:

Step 1: Right Click on Stored Procedures category in required database and select Filter > Filter Settings:

image

Step 2: In the Filter Settings dialog box, specify "Sales" in Value field, and click on OK to apply filter:

image

After applying this filer Object Explorer will only show stored procedures matching the specified filter criteria, only those which have word "Sales" in their name in this case:

image

Step 3: To clear the filter and list all objects again, right click on filtered category and select Filter > Remove Filter. This will clear the filter applied.

Hope This Helps!

Vishal

SQL Server – How to Move Table to Another Schema

13.05.2013 1 comment

Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.

For example, if I create a table using below script it will be created under my default schema which is dbo:

USE [SqlAndMe]

GO

 

CREATE TABLE Employee

(

       ID     INT,

       Name VARCHAR(20)

)

GO

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      dbo

 

(1 row(s) affected)

As you can see from the output the table is currently in dbo schema.

Now to move this table to another schema using ALTER SCHEMA command, first we need to create the schema if it does not exist already. After that we can move table to new schema.

USE [SqlAndMe]

GO

 

CREATE SCHEMA HumanResources

GO

 

ALTER SCHEMA HumanResources

TRANSFER dbo.Employee

GO

 

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      HumanResources

 

(1 row(s) affected)

As you can see from the output the Employee table is now moved to HumanResources schema.

 

Hope This Helps!

Vishal

SQL Server – How to find Default data and log path for SQL Server 2012

06.05.2013 No comments

To identify default data and log directories in SQL Server 2012 you can use SERVERPROPERTY() function. In SQL Server 2012 two new parameters are added to SERVERPROPERTY() function namely, InstanceDefaultDataPath and InstanceDefaultLogPath which returns the default data and log directories respectively.

It can be used as below:

SELECT [Default Data Path] = SERVERPROPERTY('InstanceDefaultDataPath')

SELECT [Default Log Path]  = SERVERPROPERTY('InstanceDefaultLogPath')

GO

Result Set:

Default Data Path

C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATA

 

(1 row(s) affected)

 

Default Log Path

C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATA

 

(1 row(s) affected)

You can also get this information from Server Properties dialog box in SQL Server Management Studio.

To identify this information for earlier version of SQL Server you need to use xp_instance_regread extended stored procedure which returns data from registry.

 

Earlier I posted on identifying default data directory for multiple instances through registry which returns this information for all instances installed on server.

Hope This Helps!

Vishal

SQL Server – Finding TCP Port Number SQL Instance is Listening on

01.05.2013 4 comments

By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There are several options available to get the listening port for SQL Server Instance.

Here are a few methods which we can use to get this information.

Method 1: SQL Server Configuration Manager

Method 2: Windows Event Viewer

Method 3: SQL Server Error Logs

Method 4: sys.dm_exec_connections DMV

Method 5: Reading registry using xp_instance_regread

 


Let's see how you can use each of these methods in detail:

Method 1: SQL Server Configuration Manager:

Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager

Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <Instance Name>

Step 3. Right Click on TCP/IP and select Properties

image

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.

image

If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Ports textbox. Here my instance is listening on port number 61499.

 


Method 2: Windows Event Viewer:

When SQL Server is started it logs an event message as 'Server is listening on [ 'any' <ipv4> <port number>' in windows event logs. Here <port number> will be actual port number on which SQL Server is listening.

To view this using Event Viewer:

Step 1. Click Start > Administrative Tools > Event Viewer.

Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel > System and Maintenance > Administrative Tools > View event logs

Step 2. Navigate to Event Viewer > Windows Logs > Application

Step 3. Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log…

image

Step 4. You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it’s source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.

image

Step 5. Once the filter is applied, Locate message 'Server is listening on [ 'any' <ipv4> …'. As we can see from below screenshot that SQL Server Instance is running on TCP Port 61499.

image

 


Method 3: SQL Server Error Logs:

When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'

GO

Result Set:

LogDate                  ProcessInfo Text

2013-03-21 13:34:40.610  spid18s     Server is listening on [ ‘any’ <ipv4> 61499].

2013-03-21 13:34:40.610  spid18s     Server is listening on [ ‘any’ <ipv6> 61499].

 

(2 row(s) affected)

As we can see from the output that SQL Server Instance is listening on 61499.

Note: This method does not work if SQL Server Error Logs have been cycled. See sp_Cycle_ErrorLog for more information.

 


Method 4: sys.dm_exec_connections DMV:

DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port

FROM   sys.dm_exec_connections

WHERE  session_id = @@SPID

GO

Result Set:

local_tcp_port

61499

 

(1 row(s) affected)

As we can see from the output… same as above Smile

 


Method 5: Reading registry using xp_instance_regread:

Port number can also be retrieved from Windows Registry database.

We can use extended stored procedure xp_instance_regread to get port number information using below T-SQL code:

DECLARE       @portNumber   NVARCHAR(10)

 

EXEC   xp_instance_regread

@rootkey    = 'HKEY_LOCAL_MACHINE',

@key        =

'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLibTcpIpAll',

@value_name = 'TcpDynamicPorts',

@value      = @portNumber OUTPUT

 

SELECT [Port Number] = @portNumber

GO

Result Set:

Port Number

61499

 

(1 row(s) affected)

As we can see … same as above Smile Smile

Note: The above code will only work if SQL Server is configured to use dynamic port number. If SQL Server is configured on a static port, we need to use @value_name = 'TcpPort' as opposed to @value_name = 'TcpDynamicPorts'.

Hope This Helps!

Vishal

Categories: Management Views and Functions, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions Tags: 2008 R2, application event logs, check sql port, check sql server port no, configuration manager, Database, dmv, dm_exec_connextions, error logs, errorlos, event id, event id 26022, event viewer, find sql server port, finding the port number for a particular sql server instance, how to check the port number in sql server, how to determine whar sql server tcp ip port is being used, how to find port for sql, how to find sql runnig port, how to find sql server runnig port, how to find the port for ms sql server 2005, how to find the port for ms sql server 2008, how to find the port for ms sql server 2008 r2, how to find the port for ms sql server 2012, how to know sql server port number, identify sql server port, identify sql server port being used, identify tcp ip port, identify tcp ip port sql server, instance specific registry, ipall, key, local tcp port @@spid, local_tcp_port, logdate, Management Studio, processinfo, protocols for sql sevrer, Query, read registry tsql, rootkey, scripts, server is listening on, session_id, sp cycle error log, spid, sp_cycle_errorlog, SQL, Sql & Me, SQL 2005, sql 2005 port, SQL 2008, sql 2008 port, SQL 2008 R2, sql 2008 r2 port, sql 2012, sql 2012 port, Sql And Me, sql events, sql registry read reading registry using tsql reading registry using sql server, SQL Server, SQL Server 2005, sql server 2005 how to check port, SQL Server 2008, sql server 2008 how to check port, SQL Server 2008 R2, sql server 2008 r2 how to check port, sql server 2012, sql server 2012 how to check port, sql server change port, sql server configuration manager, sql server default port, sql server determine port, sql server error logs, sql server instance port, sql server listening port, sql server named instance port, sql server where can i find the tcp port configured, Sql&Me, SqlAndMe, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, SSMS, supersocketnetlib, sys.dm_exec_connections, tcp, tcp dynamic ports, tcp port, tcp/ip port number, tcpip port, TSQL, value, value_name, Vishal Gajjar, what tcp port is sql server running under, windows logs application, xp instance regread, xp read error log, xp regread, xp_instance_regread, xp_readerrorlog, xp_regread

SQL Server – How to check if you are running 32-bit or 64-bit version

22.04.2013 No comments

There are two different command you can use to check if you are running 32-bit or 64-bit version of SQL Server.

Using @@VERSION:

You can user @@VERSION system variable to determine edition and architecture of SQL Server as below:

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (Intel X86)

       Oct 19 2012 13:43:21

       Copyright (c) Microsoft Corporation

       Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

 

 

(1 row(s) affected)

This command returns both SQL Server and Operating system information. In the first line you get information about SQL Server, it's version and architecture. Here, x86 = 32-bit architecture and x64 = 64-bit architecture. Same it true for operating system.

 

Using SERVERPROPERTY():

We can also user SERVERPROPERTY() function to get information about SQL Server Edition and architecture.

SELECT SERVERPROPERTY('Edition')

Result Set:

Developer Edition

 

(1 row(s) affected)

From the output we can see that it's a Developer Edition instance and architecture is 32-bit. If it is 64-bit then '(64-bit)' will be appended to edition. i.e. For 64-bit the output will be 'Developer Edition (64-bit)'

Hope This Helps!

Vishal

Microsoft Certified Solutions Associate – SQL Server 2008 Core / Windows Server® 2008

18.04.2012 1 comment

Yesterday, I received two mails from the Microsoft Certification Program Team about two new certification earned:

1. Microsoft Certified Solutions Associate – SQL Server 2008 Core

2. Microsoft Certified Solutions Associate – Windows Server® 2008

 

These also reflected on my transcript today. These two new certifications are awarded due to changes in Microsoft Certification Program.

I received MCSA – SQL Server 2008 Core as I already have 70-432 and 70-448.

image

Image Source & More Info: Microsoft SQL Server 2012 Certification

And, I received MCSA – Windows Server 2008 as I already have 70-640, 70-642 and 70-646.

image

Image Source & More Info: Microsoft Windows Server Certification Overview

 

Check the links above to learn more about new certification programs.

To learn more about SQL Server certification changes, check out this webcast by Kendra Little (b | t) – SQL Server 2012 Certification Changes

 

Hope This Helps!

Vishal

TSQL – Concatenate Rows using FOR XML PATH()

27.04.2011 31 comments

This is probably one of the most frequently asked question – How to concatenate rows? And, the answer is to use XML PATH.

For example, if you have the following data:

— © 2011 – Vishal (http://SqlAndMe.com)

USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],
            SUB.Name AS [Sub Category]
FROM        Production.ProductCategory CAT
INNER JOIN  Production.ProductSubcategory SUB
            ON CAT.ProductCategoryID = SUB.ProductCategoryID

image

The desired output here is to concatenate the subcategories in a single row as:

image

We can achieve this by using FOR XML PATH(), the above query needs to be modified to concatenate the rows:

— © 2011 – Vishal (http://SqlAndMe.com)
USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],
            STUFF((    SELECT ',' + SUB.Name AS [text()]
                        — Add a comma (,) before each value
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') — Select it as XML
                        ), 1, 1, '' )
                        — This is done to remove the first character (,)
                        — from the result
            AS [Sub Categories]
FROM  Production.ProductCategory CAT

Executing this query will generate the required concatenated values as depicted in above screen shot.

Hope This Helps!

Vishal