Showing posts with label T-SQL. Show all posts

June 1, 2015

SQL Server, avoid encoding special character when using FOR XML PATH

Tejas Shah Comma Separated List, Encoding, For XML, For XML PATH TYPE, SQL Server, SQL Yoga, String Concate, T-SQL, Tejas Shah No comments

From SQL 2005, we are using FOR XML PATH('') for the string concatenation. I have also mentioned the same in very old blog: Generate Comma Separated List with SELECT statement.

Today, we face an issue when special character gets encoded and we hate to have special character in my string.

e.g: Table has records like:
1. Gynecology & Obstetrics
2. Dermatology

Expected output (after concatenating two rows): Gynecology & Obstetrics, Dermatology.

When we used FOR XML PATH, we got the output as:
Gynecology & Obstetrics,Dermatology ("&" character is encoded and it lists out "&")

This output was not the one which we were expected. To get the expected output, we need to find workaround and we finally get it as:

SELECT STUFF(((
        SELECT ',' + RTRIM(CONVERT(VARCHAR(50), sm.Speciality))
        FROM Table1 T2
        INNER JOIN Table2 sm ON sm.ID = t2.Speciality_ID
        FOR XML PATH(''),TYPE).value('.[1]', 'varchar(max)')
   ), 1, 1, '')

After adding "TYPE", we are able to get the expected output: Gynecology & Obstetrics, Dermatology

Reference : Tejas Shah (http://www.SQLYoga.com)

T-SQL to find out Space by File Group

Tejas Shah FileGroup, SQL Server, SQLYoga, T-SQL, Tejas Shah No comments

T-SQL script to find out SQL SERVER FileGroup space details:

SELECT
 fg.groupname AS 'File Group',
 Name, 
 CONVERT (Decimal(15,2),ROUND(f.Size/128.000,2)) 
  [Currently Allocated Space (MB)],
 CONVERT (Decimal(15,2),
 ROUND(FILEPROPERTY(f.Name,'SpaceUsed')/128.000,2))
  AS [Space Used (MB)],
 CONVERT (Decimal(15,2),
 ROUND((f.Size-FILEPROPERTY(f.Name,'SpaceUsed'))/128.000,2))
  AS [Available Space (MB)]
FROM dbo.sysfiles f (NOLOCK)
JOIN sysfilegroups fg (NOLOCK) ON f.groupid = fg.groupid
ORDER BY fg.groupname

SQL SERVER: T-SQL to read all attributes of the XML

Tejas Shah Read all Attributes of XML, Read XML T-SQL, SQLYoga, T-SQL, Tejas Shah No comments

We have reviewed how to read XML with SQL server in this Post, and how to read XML with Namespace in this POST. Today I would like to share how to read all attributes of the XML and have result in Table format.

Recently, my team assigned one requirement to import XML file. To read an XML file is achieved by following this POST but here, we have a requirement to read each Attribute name and Attribute Value to store that info in normalized table by comparing Attribute Name. Let me explain with example.

XML:

Expected Result:

SQLYoga Read All Attributes of the XML with T-SQL #1

Query to achieve the same:

DECLARE @XML AS XML

SELECT @XML = ''

SELECT CAST(x.v.query('local-name(.)') AS VARCHAR(100)) AS AttributeName
 ,x.v.value('.', 'VARCHAR(100)') AttributeValue
FROM @XML.nodes('//@*') x(v)
ORDER BY AttributeName

Here, We have used “@*”, which gives us way to read the attributes for the ROOT tag and “local-name” gives is the Attribute name. That’s it.

Reference: Tejas Shah ( www.SQLYoga.com )

SQL Yoga: Parse XML with namespace with SQL SERVER

Tejas Shah Parse XML, Read XML T-SQL, SQLYoga, T-SQL, Tejas, XML, XMLNAMESPACES No comments

Recently, we were working on XML having namespace and we need to read that XML to fetch the information from the XML.

Here, is the Sample XML which we are trying to parse:

DECLARE @x XML
SELECT @x = '
    <X
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1">
        <info>Sample XML TEST</info>
    </X>
'

If we need to fetch the “info” element with T-SQL we can write query as follows:

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/search/local/ws/rest/v1')
SELECT x.v.value('info[1]','VARCHAR(50)')
FROM @x.nodes('/X') x (v)

Here, we have to use “WITH XMLNAMESPACES”, so we can specify the namespace of the XML that we are reading, else it wont give us any result.

SQL Yoga – Read XML with T-SQL statement in SQL SERVER

Tejas Shah SQL Server, SQLYoga, T-SQL, Tejas Shah, XML, XML PATH No comments

In my previous post, “XML Result sets with SQL Server”, we review to generate result sets in XML from SQL server. Then I got a comment from the team, to also have post to read XML in SQL Server.

To read XML in SQL server, is also simple. Lets read the XML which is created by XML PATH in previous post.

Read XML Elements with T-SQL:

DECLARE @SQLYoga TABLE(
    ID INT IDENTITY,
    Data VARCHAR(50),
    CreatedDate DATETIME DEFAULT(GETDATE()),
    CreatedBy INT
    )
INSERT INTO @SQLYoga(Data)
SELECT 'SQLYoga'
UNION ALL
SELECT 'Tejas Shah'
UNION ALL
SELECT 'Generate XML'

DECLARE @xml XML
SELECT @xml = (
        SELECT    *
        FROM    @SQLYoga
        FOR XML PATH('Record'), ROOT('Records')
    )
SELECT @xml

Now, please find query to read the query to read XML generated above:

SELECT

        x.v.value('ID[1]', 'INT') AS ID,
        x.v.value('Data[1]', 'VARCHAR(50)') As Data,
        x.v.value('CreatedDate[1]', 'DATETIME') AS CreatedDate
FROM    @xml.nodes('/Records/Record') x(v)

This query generates the output as follows:

That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this.

Reference: Tejas Shah (www.SQLYoga.com)

SQLYoga – XML Result sets with SQL Server

Tejas Shah For XML, Generate XML, SQL Server, SQLYoga, T-SQL, Tejas Shah, XML, XML PATH, XML ROW No comments

Recently, found that people making much complex code to have an XML in application. I have found that they return result set in Table to the application and have applied code to convert that data table to XML by Asp. Net application. When I review the process, I have suggested that why don’t you use the XML feature of the application. They surprised the simple use of it and make their life easy. Let me have sample query to generate XML result sets in SQL Server database.

There are two ways to have an XML from query:
1. FOR XML RAW
2. FOR XML PATH

Please find following sample queries where both of the way are being used:

Lets create sample data

Generate XML with XML ROW

SELECT *
FROM @SQLYoga
FOR XML RAW, ROOT('Records')

XML RAW, returns the XML by each record with row element and all columns as attributes in the XML.

Generate XML with XML PATH

SELECT *
FROM @SQLYoga
FOR XML PATH('Record'), ROOT('Records')

XML PATH, returns the XML by each record with separate element and also columns as element within respected element of the record.

That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this.

Reference: Tejas Shah (www.SQLYoga.com)

SQL SERVER: T-SQL to find out when database was restored

Tejas Shah SQL, SQL Developer, SQL Yoga, T-SQL, Tejas Shah, Tips No comments

Recently, we have found that our testing database has been restored on the server and we lost our recent work. We have to identify that at what time the database has been restored, so we can know that what are the changes need to be restored again on the database.

We have found that MSDB database keeps information for each restore we have made on the server and luckily we have found the time and user we had restored the database. Let me share the simple script that I have found:

SELECT    *
FROM    msdb.dbo.RestoreHistory WITH (nolock)
WHERE    destination_database_name = '<Database Name>'
ORDER BY restore_date DESC

Reference: Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: T-SQL to display Job history

Tejas Shah SQL Job, SQL Server, SQL Yoga, SQLYoga, T-SQL, Tejas Shah No comments

We have multiple jobs running on the server as per the schedules. We need to have the status of the each job at the end of the day, so we can know that which jobs runs successfully and which gets failed.

We have found the table, sysjobhistory, which contains history and get the list as per oyur requirements. Let me share the query:

SELECT
    j.name As JobName,
    run_date As RunDate,
    RIGHT('000000' + CONVERT(varchar(6), Run_Time), 6) AS RunTime,
    CASE h.run_Status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
    ELSE ''
    END AS JobStatus,
    RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) As JobDurationinHHMMSS
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j on h.job_id = j.job_id
    AND h.step_id = 0
ORDER BY j.Name, h.run_date

Here, we have used “h.step_id = 0”, as we just concern about SQL server job status, we are not concerned about each step of the job. This query gives us result as follows:

We have also configured, Notification email, which sends an email whenever job is getting failed. This query is used to review all job statuses together. Please let me know your comments

Reference: Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: Display Row count of all the tables

Tejas Shah Row Count, sp_MSforeachtable, SQL Server, T-SQL, Tejas Shah 5 comments

Recently we have performance issue in SQL SERVER and we decided to move some large tables to different file group, so we can improve SQL SERVER performance, as it uses other disks I/O. Now challenge is to identify large tables from many tables (approx 700 tables).

There are few ways to display row count of all the tables:

Use Procedure: sp_msForEachTable (Simplest way)

Read information from system tables ( approximate count, Preffered)

Let me explain both of the method:

1. Use Procedure: sp_msForEachTable:

This is the simplest way that we can make COUNT(*) to each table to find number of rows from each table. This is undocumented stored procedure,"sp_msForEachTable" gives ability to make a query to all tables.

EXEC sp_msForEachTable 'SELECT ''?'' AS TableName,COUNT(*) AS NoOfRows FROM ?'

Its nothing but just a simple dynamic SQL statement where "?" will be replaced by table name. So query will be like:

SELECT COUNT(*) FROM BuildVersion

This procedure, "sp_msForEachTable", will loop to each table for current database and count rows from each table. So it gives result like:

By this way, we can get row count from all the tables for current database. But question is: How to use this result set? How to find out which table has maximum rows? To find out this, I need to check each table's rows manually and then only I can find out which table has maximum rows. This is time consuming and not the good way to find out table with maximum rows. Let see another method on which we can find it out it easily.

2. Read information from system tables:

By reading system tables, we can also find out rows from each table. We can use following query to find out number of rows from each table.

SELECT 
 st.Name AS TableName,
 SUM(  
  CASE  
   WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
   ELSE 0  
  END  
 ) AS NoOfRows
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
GROUP BY st.name

NOTE: This method is used to get an *approximate* count for all tables.

This query solved my purpose. In this query, it will give result in single result set, so we can manipulate it easily.

E.g. List out all tables and sort it out based on the NumberOfRows.
So, my query will be:

SELECT 
 st.Name AS TableName,
 SUM(  
  CASE  
   WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
   ELSE 0  
  END  
 ) AS NoOfRows
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
GROUP BY st.name
ORDER BY NoOfRows DESC

Let me know your opinions.

SQL SERVER: Find executing queries

Tejas Shah DBA, Executing queries, T-SQL, Tejas Shah No comments

Recently my company owner reported that SQL SERVER is running slow. I searched for it and get following T-SQL query. SQL SERVER provides dynamic views, which contains current information about all details.

SELECT OBJECT_NAME(ObjectID) as ObjectName,
  st.Text, 
  DB_NAME(database_ID) as dbname,
  Blocking_session_ID as BlockingSessionID,  
  *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

It will give us details like about running SQL Objects, with Database name:

Reference : Tejas Shah http://www.SQLYoga.com

SQL SERVER: How to Read Excel file by TSQL

Tejas Shah Import Excel file, SQL Developer, SQL Server 2005, T-SQL, Tejas Shah 12 comments

Many times developers asked that, they want to import data from Excel file.

We can do this by many ways with SQL SERVER.

1. We can use SSIS package
2. Import/Export Wizard
3. T-SQL

Today, I am going to explain, How to import data from Excel file by TSQL.

To import Excel file by TSQL, we need to do following:

1. Put Excel file on server, means we need to put files on server, if we are accessing it from local.

2. Write following TSQL, to read data from excel file

SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLYoga.xls', [SQL$])

NOTE: Here, Excel file is on "C:\" named "SQLYoga.xls", and I am reading sheet "SQL" from this excel file

If you want to insert excel data into table,

INSERT INTO [Info]
SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$])

That's it.

SQL SERVER: How to remove cursor

Tejas Shah Cursor, Query Performance, SQL Developer, SQL Server 2005, T-SQL, Table Variable, Tejas Shah 3 comments

Many times developer ask me that How can they remove Cursor?

They need to increase Query Performance, that's why they need to remove SQL SERVER Cursor and find the alternate way to accomplish the same.

Please find this code to remove cursor with Table variable:

--declare table to keep records to be processed
DECLARE @Table AS TABLE(AutoID INT IDENTITY, Column1 VARCHAR(100), Column2 VARCHAR(100))
--populate table variable with data that we want to process
INSERT INTO @Table(Column1, Column2)
SELECT    Column1, Column2
FROM    <Table>
WHERE    <Conditions>
--declare variables to process each record
DECLARE @inc INT, @cnt INT
--Assign increment counter
SELECT @inc = 1
--Get Number of records to be processed
SELECT @cnt = COUNT(*)
FROM @Table
WHILE @inc <= @cnt BEGIN
    --As we have AutoID declared as IDENTITY, it always get only one record.
    --Get values in Variable and process it as you want.
    SELECT    @Column1 = Column1,
            @Column2 = Column2
    FROM    @Table
    WHERE    AutoID = @inc        
    --do your calculation here
    ....
    ....
         
     --Select next record
     SET @inc = @inc = 1 
END

By this way, we can remove CURSOR by Table variable.

It is quite easy to implement.

One more benefit is: It will process one record at a time, so it locks only that record at a time.

Let me know if you have any questions.

Community TechDays at Ahmedabad - Great Event

Tejas Shah CommunityTechDays, DBA, SQL, T-SQL No comments

There was a great event held by Microsoft and PASS (Professional Association of SQL Server) in Ahmedabad on 3rd October 2009. This was very big event where 250+ attendees attend the excellent session of Vinod Kumar, Pinal Dave, Jacob Sebastian and Prabhjot Singh Bakshi. There were Four technical sessions by these excellent speakers.

First session is of Vinod Kumar. He is Microsoft Evangelist. There was excellent session of Three hours where he give features of Windows 7 and Office 2010.

Second session is of Pinal Dave, SQL MVP. He presented session on the subject - "SQL Server - The Other Side of Index". This was also excellent way to know more about SQL Index. and How to use index. That is really nice session for SQL Developers to know, How to optimize query. It gives proper understanding of indexes.

Third session is of Jacob Sebastian, SQL MVP. He presented session on '"The Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server". This was also very great session where developer can get more idea about When exception occur and how to handle that exception. It was presented in a way so all attendees get clear idea about it and understand it properly.

Forth and last session is of Prabhjot Singh Bakshi, MCT(Microsoft Certified Trainer). He presented session on "NET Framework 4.0". He also presented very nice session about featured of .Net Framework 4.0. He developed .Net code to give clear understanding of new features.

Click with Vinod kumar and Jacob Sebastian:

Overall, that was excellent day for Ahmedabad. I hope this type of TechDays scheduled in near future.

Reference: Tejas Shah ( http://www.SQLYoga.com)

SQL SERVER: Agenda of Community TechDays at Ahmedabad

Tejas Shah CommunityTechDays, Microsoft, SQL, T-SQL No comments

SQL SERVER: Difference between DELETE and TRUNCATE commands

Tejas Shah DELETE, SQL, SQL Tips, T-SQL, Tejas Shah, TRANSACTION, TRUNCATE 1 comment

We interviewed many people my company as recruitments are going on for developers. When I asked this SQL SERVER question to person "What is difference between DELETE and TRUNCATE in SQL SERVER?".

I got the following answers from most of them are, which are incorrect:

1. I can not use WHERE condition with TRUNCATE command
2. I can not use TRUNCATE command if foreign key is there on table.
3. TRUNCATE is faster than the DELETE, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files. etc..

These answers are correct. I also got this answer, which is Incorrect:

"DELETE can be rolled back while TRUNCATE can not be rolled back"

I asked them what does it mean?, give me an example. See what they say, if I have Transaction and if I have used DELETE then ROLLBACK will let them back to original state. In case of TRUNCATE within Transaction, will not allow me to original state.

This is incorrect. We can ROLLBACK changes made by DELETE and TRUNCATE if the it is used in Transaction. Lets see this in detail by example:

Create one table table and insert some data in it like:

CREATE Test TABLE(
ID INT IDENTITY
Text VARCHAR(5)
)
GO
INSERT INTO Test(Text) VALUES('A')
INSERT INTO Test(Text) VALUES('B')
INSERT INTO Test(Text) VALUES('C')
INSERT INTO Test(Text) VALUES('D')
GO

Lets try to use TRUNCATE within Transaction as follows:

BEGIN TRAN --Start Transaction
TRUNCATE TABLE Test --Use this command
--Check the data from table. See transaction is still in progress not commit/Rollback yet
--There is no row in table, as all rows were deleted from above TRUNCATE statement
SELECT * FROM Test 
ROLLBACK --Rollback this Transaction
--Lets see now what values should be there in table after ROLLBACK
SELECT * FROM Test

So, we found that we can found all values in the table. We can ROLLBACK the TRUNCATE command as same as other commands.
This is place where most of the developers can not give accurate answer. I hope by this article they can have clear idea.

Let me know if there is any confusion.

CONCLUSION:

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.

Reference: Tejas Shah( http://www.SQLYoga.com )

SQL SERVER: Reset Setup Values, when SQL SERVER is started/restarted

Tejas Shah Auto Execute Stored Procedure, DBA, SQL, SQL Server 2005, SQL Services, T-SQL, Tejas Shah No comments

We have a requirements to clear all setup values when SQL SERVER is started/restarted and we need to setup default values to setup table.

I found one Stored Procedure provided by MS SQL SERVER. Let me share it with all of you.

SQL SERVER provides this SP: “sp_procoption”, which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way. Let me show you how to use it

Syntax use this SP:

EXEC SP_PROCOPTION 
    @ProcName = 'SPNAME',
    @OptionName = 'startup',
    @OptionValue = 'true/false OR on/off'

@ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in “master” database.

@OptionName, should be “startup” always.

@OptionValue, this should be set up to execute this given sp or not. If it is “true/on”, given sp will be execute every time when SQL SERVER is started. If it is “false/off”, it will not.

That’s it, lets take an example.

I have one Database called Test, I have created setup table:

CREATE TABLE SetupTable(
Seq INT IDENTITY,
Code VARCHAR(100)
)

Lets insert some default values to this table:

INSERT INTO SetupTable VALUES('A')
INSERT INTO SetupTable VALUES('B')
INSERT INTO SetupTable VALUES('C')
INSERT INTO SetupTable VALUES('D')

What I need to do is, I need to wipe out this values when SQL SERVER is started and fill it with the same default values, because these values might be updated by application.So, I created one stored procedure in master database, named,

CREATE PROC ClearAllData
AS

DELETE
FROM    Test.dbo.SetupTable

INSERT INTO SetupTable VALUES('X')
INSERT INTO SetupTable VALUES('Y')
INSERT INTO SetupTable VALUES('Z')

and set up this stored procedure as auto executed every time when SQL SERVER is started as:

EXEC SP_PROCOPTION 
    @ProcName = 'ClearAllData',
    @OptionName = 'startup',
    @OptionValue = 'true'

Now, restart SQL SERVICES, and you find that old values will be deleted and new values with ‘X’, ‘Y’, and ‘Z’ will be inserted automatically.

If now you want to stop it to execute automatically, we just need to execute this with “false” as:

EXEC SP_PROCOPTION 
    @ProcName = 'ClearAllData',
    @OptionName = 'startup',
    @OptionValue = 'false'

I hope this is very clear to use this feature.

Reference : Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: Read values from Comma Separated variable

Tejas Shah Comma Separated List, DBA, MS SQL Server, Read Values from Comma Separated variable, SQL, SQL Tips, T-SQL, XML 6 comments

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column. Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this. Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.

Example:

I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.

Proposed Solution:

Convert VARCHAR variable to XML as follows:

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

So this statement will generate XML from VARCHAR value as follows:

So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:

SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)

This will give me result set as: ( as separate table)

So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.

So my Procedure looks like:

CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
    @str    VARCHAR(100)
AS

DECLARE @XmlIDs XML

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

UPDATE    TableName
SET        Flag = 1
WHERE    ID IN(
                SELECT x.v.value('.','INT')
                FROM @xmlIDs.nodes('/IDs/ID') x(v)
            )

So, it is very easy to read values from Comma separated value.

Let me know if it helps you.

SQL SERVER: Difference between OpenQuery and OpenRowSet

Tejas Shah DBA, OpenQuery, OpenRowSet, SQL, SQL Server 2005, SQL Tips, T-SQL, Tejas Shah 4 comments

Today, one developer asked me what is the difference between OpenQuery and OpenRowSet.

Let me share this thing with all of you.

Syntax for both the command:

OPENQUERY ( linked_server ,'query' )

OPENROWSET
( 'provider_name' , 'datasource' ; 'user_id' ; 'password'
, { [ catalog. ] [ schema. ] object | 'query' }
)

Difference is:
OpenQuery uses a predefined linked server,
While OpenRowSet has to specify all the connection options. So with OpenRowSet you can query to your remote SQL server from local.

Else it's the same.

Let me know if it helps you in any way.

SQL SERVER: How much space occupied by Each Table with sp_MSforeachtable procedure

Tejas Shah Iterate Each Table, sp_MSforeachtable, SQL, SQL Server 2005, SQL Tips, T-SQL, Tejas Shah No comments

Today, I came across requirement where I need to perform an action on all of the tables within a database.

For example, How much space occupied by each table.

I found undocumented Procedure: sp_MSforEachTable in the master database.

The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'sp_spaceused [?]'

So, We can use sp_MSforeachtable procedure when we need to loop through each table.

Let me know if it helps you in any way.

SQL SERVER: Unable to Launch Activity Monitor. How to add VIEW SERVER STATE Permissions to launch Activity Monitor

Tejas Shah Activity Monitor, Executing queries, SQL, SQL Server 2005, T-SQL, Tejas Shah No comments

Activity Monitor, is used view to current executing queries, Query status, Start Time, Host, Blocking or not, Database etc.

Last week, one of my developer has one issue. When he try to open Activity Monitor, System gives the following Error:

I found that this user need to get VIEW STATE Permissions, Lets see how to give this permission to the user.

We can give permission to user by this two ways:

1. T-SQL: GRANT VIEW SERVER STATE TO YourLogin

2. GUI:

Step 1: Right mouse click the SQL Server and go to Properties. You need to give GRANT to “View server State’ for that user.

Let me know it it helps you.

June 1, 2015

March 28, 2014

September 4, 2013

XML:

Expected Result:

Query to achieve the same:

July 8, 2013

May 6, 2013

May 3, 2013

October 16, 2012

October 10, 2012

August 23, 2010

August 1, 2010

December 3, 2009

December 2, 2009

October 7, 2009

September 16, 2009

September 5, 2009

July 15, 2009

May 4, 2009

April 15, 2009

March 26, 2009

March 22, 2009

About Me

Popular Posts

Blog Archive