Gokul's Blog


Leave a comment

Get the Rowcount of All Tables in a Database SQL Server

Method-1:

SELECT sysobjects.Name AS TableName, sysindexes.Rows as Rows
FROM
    sysobjects
    INNER JOIN sysindexes
    ON sysobjects.id = sysindexes.id
WHERE
    type = ‘U’
    AND sysindexes.IndId < 2
ORDER BY
    sysobjects.Name

 

Method-2:

select convert(varchar(30),object_name(id)) [Table Name], rows from sysindexes
where object_name(id) not like ‘sys%’ and indid = 1
order by object_name(id)

Advertisements


Leave a comment

Debugging with SP_who & Dbcc Inputbuffer (SPID)

When dealing with the problem of connection leaks SP_who and SP_who2 are very helpful to find the issue.

With some googling and writing some code was able to create some code which will help us to determine the problem causing procedure, functions, etc.

drop table #retTable
drop table #DbccOutput

create table #retTable(
    spid smallint,
    ecid smallint,
    status nchar(30),
    loginname nchar(128),
    hostname nchar(128),
    blk char(5),
    dbname nchar(128),
    cmd nchar(16),
    request_id INT
)

INSERT INTO #retTable EXEC sp_who
SELECT * FROM #retTable WHERE dbname = ‘MyTableName’

declare @iSpidMax varchar(6)
declare @sSql varchar(50)

Create Table #DbccOutput ( spid integer, EventInfo varchar(256) )
Create Table #DbccOutputTemp ( EventType varchar(256), Parameters integer, EventInfo varchar(256))
Declare MyCursor Cursor For
  Select Distinct spid From #retTable
Open MyCursor
Fetch Next From MyCursor Into @iSpidMax
While @@Fetch_Status = 0
Begin
  Set @sSql = ‘DBCC inputbuffer(‘ + Convert( varchar(3), @iSpidMax ) + ‘)’
  Insert Into #DbccOutputTemp Exec ( @sSql )
  Insert Into #DbccOutput ( spid, EventInfo )
    Select @iSpidMax As spid, EventInfo From #DbccOutputTemp
  Delete #DbccOutputTemp
  Fetch Next From MyCursor Into @iSpidMax
End
Close MyCursor
Deallocate MyCursor

select * from #DbccOutput
select distinct eventinfo from #DbccOutput


Leave a comment

SQL Snippet Series

To get a list of tables in a database.

select table_name as Name from INFORMATION_SCHEMA.Tables
where TABLE_TYPE ='BASE TABLE'
order by name asc
To Restore a Database:
RESTORE DATABASE [MyDatabase] FROM DISK = N'E:\Backups\MYDBBackup_20080709.DMP' WITH FILE =1,
MOVE N'XList_Import_Data' TO N'E:\Mssqldata\DataInst\MyDB.MDF',
MOVE N'XList_Import_Log' TO N'E:\Mssqldata\LogInst\MyDB.LDF', NOUNLOAD, REPLACE, STATS = 10
GO
I would like to add some more information which I saw on SQL Server central.com. This content is taken from 
the following article: http://www.sqlservercentral.com/articles/T-SQL/63471/
Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005
USE [your_database_name_here];
GO
SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
ORDER BY [name];
GO

This coding is based on filter by the object “type”. Use the following table to determine the type of object you want returned, and then adjust the WHERE clause to only return those type(s) you wish to be returned. This listing only lists views, functions, and procedures. There are other types which you can also filter, be sure to review BOL for further information on the types

Type Initial(s)
 Type Of Object Returned
FN SQL Scalar Function
IF Inline Table Valued Function
P SQL Stored Procedure
PC CLR Stored Procedure
TF SQL Table Valued Function
V View
X Extended Stored Procedure
User-Defined Stored Procedures

USE [your_database_name_here];
GO
SELECT * FROM sys.all_objects
WHERE ([type] = ‘P’ OR [type] = ‘X’ OR [type] = ‘PC’)
AND [is_ms_shipped] = 0
ORDER BY [name];
GO

EDIT: Aug/19/2008.

Create a Comma Delimited List Using SELECT

USE Northwind
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + LastName
FROM Employees
SELECT @listStr
GO

The above script can be used to get the list of rows separated by “,” for a particular column. Article source

Edit: 09/08/2008

Get the duplicate values in a SQL column

SELECT     tablefield, COUNT(tablefield) AS dup_count
FROM         table
GROUP BY tablefield
HAVING     (COUNT(tablefield) > 1)

And to get the list of duplicate values.

SELECT *  FROM table
WHERE tablefield IN (
SELECT tablefield  FROM table
GROUP BY tablefield  HAVING (COUNT(tablefield ) > 1) )

Article Source

 

Edit: 09/19/2008

sp_who2 : Displays the current open connections

DBCC INPUTBUFFER(<spid>): Display the last executed command by the specified SPID.


Leave a comment

SQL Proc to decode HTML(including Unicode Characters)

One of my Co-worker sent a note about an interesting Stored procedure he wrote for decoding HTML content. I thought this would be helpful at some point for me/someone.

If you have any questions,please contact the author at the email mentioned below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- EXEC UrlDecode '%c3%a1'
-- EXEC UrlDecode '%4D'

Create PROCEDURE UrlDecode
    -- Add the parameters for the stored procedure here
    @URL varchar(2000)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Position INT,
        @Base CHAR(16),
        @High TINYINT,
        @Low TINYINT,
        @NewHigh INT,
        @Pattern CHAR(21)

    SELECT    @Base = '0123456789abcdef',
        @Pattern = '%[%][0-9a-f][0-9a-f]%',
        @Position = PATINDEX(@Pattern, @URL)

    SELECT    @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
            @Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1

    WHILE @Position > 0
    BEGIN

            IF (@High & 15) = 12 -- xC0 
            BEGIN
                SELECT @NewHigh = @Low * POWER(2, 6) --Shift Low 6 bits

                SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 4, 1), @Base) -1
                SELECT @Low = CHARINDEX(SUBSTRING(@URL, @Position + 5, 1), @Base) -1

                SELECT @URL = STUFF(@URL, @Position, 6, CHAR(@NewHigh | (16 * @High) | @Low))

                SELECT @High = 0, @Low = 0, @NewHigh = 0

            END
            ELSE
            BEGIN
                SELECT @URL = STUFF(@URL, @Position, 3, CHAR(16 * @High | @Low))

            END

            SELECT @Position = PATINDEX(@Pattern, @URL)
            IF @Position > 0
            BEGIN
                SELECT    @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
                        @Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1
            END
    END

    SELECT @URL

END
GO

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

You can contact him at Gordon’s Email (remove 3-AT’s)