Get Server name, Database name, Schema name or Object name

There is a simple T-SQL function that Returns the specified part of an object name. using this function we can retrieve object name, owner name, database name, and server name from a fully qualified object name. This function doesn't require the object name that is provided is in the database, its only returns the specified part of the specified object name.

PARSENAME ( 'object_name' , object_piece )

object_piece

Object_piece is the part that you want to retrieve.
1 = Object name 
2 = Schema name
3 = Database name
4 = Server name

what this function actually doing is, it getting the object name and split the object name by period (‘.’) and returning the specified position in Object_piece.

SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',1) AS ObjectName
      ,PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',2) AS SchemaName
      ,PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',3) AS DatabaseName
      ,PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',4) AS ServerName

if you smart enough you can use this for many things, such as separating extension from the file name or parse an IP address. Just remember you are working backwards.

How to add line break to a string

when we creating dynamic sql it is easy to read or troubleshoot the SQL query if we format it. I’m always adding line breaks to my dynamic queries using following code.
Here I’m just declaring a variable and setting the CR and line breaks to it. you can use one of these CHAR(13) for CR and CHAR(10) is for DOS/Windows style CRLF Line breaks.

DECLARE @line_break NVARCHAR(2);
SET @line_break = CHAR(13) + CHAR(10);

PRINT '-------------------------'+@line_break+'Line 1' + @line_break + 'Line 2' + @line_break +'-------------------------'

result…
-------------------------
Line 1
Line 2
-------------------------

How to monitor page split in SQL Server

I had a question when creating or rebuilding a index, the fillfactor value that I set to it is the perfect value for my requirement. I have found a query that use a undocumented function and read the transaction log and identify the indexes that are splitting.



SELECT AllocUnitName,COUNT([AllocUnitName]) [Splits]
FROM ::FN_DBLOG(NULL,NULL)
WHERE Operation=N'LOP_DELETE_SPLIT' AND PARSENAME(AllocUnitName,3)<>'sys'
GROUP BY AllocUnitName




there is another way to find the page split with Extended events. for more details go to Michael Zilberstein's Blog

Disable constraints on a table

Sometimes it's useful to disable one or more constraints on a table, do something significant, and then re-enable the constraints after you're done. This is most often done to improve performance during a bulk load operation, Some type of Database cleanup process.

How to: Enable/Disable single constraint

According to SQL Server Books Online, we can disable constraints using the ALTER TABLE statement. for more details go to ALTER TABLE (Transact-SQL)

for an example:

/***** disable the FK_TABLE2_TABLE1 constraint on TABLE2 *****/

ALTER TABLE dbo.TABLE2 NOCHECK CONSTRAINT FK_TABLE2_TABLE1

/***** enable the FK_TABLE2_TABLE1 constraint on TABLE2 *****/

ALTER TABLE dbo.TABLE2 CHECK CONSTRAINT FK_TABLE2_TABLE1

How to: Enable/Disable all constraint

The following example shows how to disable all constraints on a table.
here i'm creating T-SQL statement for all tables using INFORMATION_SCHEMA View and execute them

--Disable
DECLARE @SQL VARCHAR(MAX) 
SELECT @SQL = ISNULL(@SQL,'') + 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL;' 
FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE' 
EXEC(@SQL) 
go 

--Enable 
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = ISNULL(@SQL,'') + 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL;' 
FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE' 
EXEC(@SQL)

How to create a comma delimited list using SELECT clause from table column?



How to create a comma delimited list using SELECT clause from table column?

USE tempdb;
GO

/*Create sample table and data*/
CREATE TABLE sampleTable (id INT, VALUE VARCHAR(150))
GO

INSERT dbo.sampleTable
        ( id, VALUE )
VALUES  ( 10, 'Value 1' ),( 11, 'Value 2' ),( 12, 'Value 3' ),( 13, 'Value 4' ),( 14, 'Value 5' )
GO

DECLARE @CSV_str NVARCHAR(500);
/*Collecting all values in Value column to @CSV_str variable*/
SELECT @CSV_str = ISNULL(@CSV_str + ',','') + VALUE FROM dbo.sampleTable

PRINT @CSV_str


/*drop sample table*/

DROP TABLE dbo.sampleTable


Connecting to SQL Server over the Internet

         Once I had to analyze a SQL Server in a remote site every day, to do the analyzing I had to get a RDC to the machine or go to that site. This was so irritating for me because working with a RDC is like watching a slow-motion movie, and I don’t like to go outside in daytime ;-).

So I Google to find a way to connect directly to the all SQL server instances in all sites over the internet. There was some Q&A, blogs and some topics in MSDN but I couldn’t find an every steps to configure the SQL server, Firewall, Router in one place. So I decide to write this article for help newbies to the SQL Server.

 

How to: configure SQL Server to Connect Remotely.

1. Go to SQL Server Configuration Manager.
2. Select SQL Server Network Configuration.
3. Select Protocols for your Server Instance.
4. Double Click on protocol TCP/IP and go to IP Addresses Tab

If you have a default Instance you will see TCP Port set to 1433 in IPAll (Default instances of the Database Engine use TCP port 1433 by default.), if there are no port have set to TCP port, set it to 1433.

 

For the named instance SQL server dynamically assign an unused TCP port number the first time the instance is started. If that port is used by another application SQL server will assign an unused TCP port. So we can’t use a dynamic port to connect remotely to the SQL Server. Assign a new TCP port number to TCP port.

5.  Go to Protocol tab and Set Enabled to Yes. Click Ok

6.  Go to SQL Server Services Restart the SQL server.

7.  Start the SQL Server Browser Service (if u need to remote connections to the Dedicated Administrator Connection this service should be enabled.)

 

Now the SQL Server Configuration part is done.

How to: Configure a Windows Firewall for Database Engine Access

 

                To access an instance of the SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server to allow access. To do so we need to create a firewall rule to open a port to connect with the SQL server

 

To create a new firewall rule using the New Rule wizard

• On the Start menu, click Run, type WF.msc, and then click OK.
• In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule.

• Select Port Click Next

• Enter the SQL Server Listening port(s) (this port is that we configured in SQL Server Configuration Manager, TCP/IP port) and click next.

• Select allow the connection, Click next.

• Select Appropriate profiles click next.

• Give a Name click finish

How to: configure a Router.

      Open the Port Forwarding or Applications and Gaming tab. Each router will be slightly different, but look for these two options. If you don't see them, try Advanced Settings and look for a Port Forwarding subsection. You will see something like below.

 

Application: Is the name (Give “SQL” or any name u like)

External Port: You will connect to the SQL server using this port

Internal Port: this should be the port that we configured in SQL Server Configuration Manager, TCP/IP port

Protocol: Select TCP

IP Address: IP address on the computer that is running SQL Server

Enabled: you know what this is right? ;-)

If you need more knowledge about port forwarding read this article.

Find the public IP

Open IE and go to Google type “My IP” and search, it will show your public IP. When we are connecting through the internet we will use this IP as our SQL server Name

 

How to: connect to a SQL server instance

 

               Now we have configured the SQL server and punch a hole on the firewall to connect to the SQL Server from outside and also we have configured the router to forward the request to the SQL server.

 

Open SQL Server Management Studio.

 

 

Type the public IP and the external port number (that you have set in router) in the Server name and give login details

You are done.

 

Click Connect…..

Convert all tables in a database to a specific schema

Create schema

CREATE SCHEMA sampleSchema
GO


Create a T-SQL statement to transfer all tables to created schema

DECLARE @sql NVARCHAR(MAX) ;
DECLARE @LINEBREAK AS VARCHAR(2)
SET @LINEBREAK = CHAR(13) + CHAR(10)
        
SELECT  @sql = COALESCE(@sql+ '; ' + @LINEBREAK , '') + 'ALTER SCHEMA [sampleSchema] TRANSFER '
        + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME])
    
FROM    INFORMATION_SCHEMA.TABLES

PRINT @sql
EXECUTE sp_executesql @sql