Change schema name of tables, store procedure, views and functions

Today i come across one situation where i need to changes my current SQL schema to some other name. due to client requirement.so i searched few article and decided to write one article on same

Below query will returns list of all tables along with the schema name from your database.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

If you want to add new schema you need to add that into sys.schemas table then only it will be accessible else it will give an error message as 

 Cannot alter the schema 'xxx', because it does not exist or you do not have permission.
 

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Excprod'))
BEGIN
    EXEC ('CREATE SCHEMA [Excprod] AUTHORIZATION [dbo]')
END

You can also view list of schema from below query and you will find new schema "Excprod" get added.

SELECT * FROM sys.schemas

Now, below query used to alter the scheme tables

ALTER SCHEMA Excprod TRANSFER dbo.Temp1
ALTER SCHEMA Excprod TRANSFER dbo.Temp2
ALTER SCHEMA Excprod TRANSFER dbo.temp3

You may also alter the schema for store procedure as well. to find the list of all store procedures from your
database just use.

select * from information_schema.routines
where routine_type = 'PROCEDURE'

below query will generate the select statement for all list of sp

select 'ALTER SCHEMA Excprod TRANSFER ' + SPECIFIC_SCHEMA + '.' + ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='procedure'

Result will be :

ALTER SCHEMA Excprod TRANSFER synprod.Usp_Response
ALTER SCHEMA Excprod TRANSFER synprod.Usp_Request

To view list of view from database use this

SELECT * FROM information_schema.VIEWS

Please comment if you want more details on this.

Convert Dataset to xml string using C#

In my previous post i explained, Save images to database using SQL. Count string occurrence and much more articles realted with asp.net, SQL. Now in this article i will going to explain how to Convert the dataset to xml string using  C# and asp.net 

below is the short and sweet way to convert the dataset into xml.

DataSet dsUser = new DataSet();

SqlCommand cmdSelect = new SqlCommand("select name,age,location,ph,cell,status from userDetails ", conn);

SqlDataAdapter sda = new SqlDataAdapter(cmdSelect);

da.Fill(dsUser);

string strUserDetails = dsUser.GetXml();

Its very simple and easy method. Please post your comments for any suggestion.

Save images to database using SQL

Most of the time we want to save store/save images to database. In order to store image on sql server we need to store that in binary format. the most easiest way to do so in execute the SQL OPENROWSET command with SINGLE BLOB & BULK options.

Let's create one sample table and insert the sample result in it.

create table imageSave

(

   [img_name] varchar(250),
   [Img_binary] varbinary(max)

)

will insert some sample result into the imageSave table.

Insert into imageSave

SELECT 'SAMPLE Image', *

FROM OPENROWSET(BULK N'F:\Images\sample-test.jpg', SINGLE_BLOB) image;

Admin(sa) & developer has rights to work with OPENROWSET command.

delete existing store procedure and tables

Delete all existing database use this scripts.
    

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?
GO



if you want to delete all existing store procedure use this scripts.

DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type = 'p'
      OPEN cur

      FETCH NEXT FROM cur INTO @procedureName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @procedureName)
            FETCH NEXT FROM cur INTO @procedureName
      END
      CLOSE cur
      DEALLOCATE cur

Hope it will help you :)

SQL Database compare software

Here is free databse compare software for Access / MY SQL / Sql Serve , Compare application will allow you to compare the structure of two SQL server databases and display all the differences between them.. The structure viewer is included on same , you can find the comparison and acoording to that you can choose to exclude that comparioson of given fields , constraints .This is a lightweight version compared to version 2.0, it very small and portable.

>Download Here

Find Maximum Connections for SQL Server.

- If you want to know how many connection get happen with your database then you can easily get those number using this technique.

Select @@MAX_Connections as MaxConn

Using this you will get the Max number of connection get allowed on an instance of sql server.

- now if you want to know how many attempted connection are then

Select @@Connections as TotalNumberOfLogin

It will gives you all the number of connection was happen when the sql server get started , that may be a successful or unsuccessful connections also.

- You can also check this all using this system store procedure call

Exec sp_monitor

This will gives you result on when server get running , cpu time , usage , amount of data sending receiving , total number of read write operation done plus is there any error on connection like that .

How to know SQL Server version and Edition

some time we just install the sql server , but still we don't know which type of version it is and edition also , basically we need that when we are upgrating current version of new version. Current there is sql server 2008 is there in market,

now how to determine which version of sql 2008 is running out machine,
- run the following T-SQL statement,

SELECT SERVERPROPERTY ('productlevel') ,SERVERPROPERTY('productversion'), SERVERPROPERTY ('edition')

you will get the result like :
RTM | 10.0.1600.22 | Enterprise Edition

RTM is product level which means - 'Release To Manufacturing'.
but what is means of RTM - is a stage on which the distribution of process begins CD of
that product get created and retial packs and then product will get shipp , this is very
long process and because of that RTM date is mostly ahead of the final release date.

10.0.1600.22 means product version.

Enterprise Edition means its a Edition there are some other editions are also there like
Developer,standard ,web,Express ,Workgroup , 64- bit Edition .

The following table lists the Sqlservr.exe version number.

Release	Sqlservr.exe
RTM	2007.100.1600.0
SQL Server 2008 Service Pack 1	2007.100.2531.0

SQL : Count String Occurrence

in SQl server, we dont have any function which can return the number of words in a perticular string .but from small tricks we can do that . now Consider here we can separating the character on space basis

example :


declare @Str VARCHAR(2000)
SET @Str = 'Hi this is Test line which count no of spaces.'
select LEN(@Str) - LEN(Replace(@Str,' ', '')) + 1


Above query will return value 10 , but if the words are separate by more than one space then it will aslo count that space. but its wrong as per the answer.
in that case Create one function which can keep multiple spaces as a single space and return proper result

Below is a Function which can remove white space and all and retrun peoper result.

CREATE FUNCTION [dbo].[WordCount] ( @inStr VARCHAR(4000) )
RETURNS INT
AS
BEGIN

DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @WordCount INT

SET @Index = 1
SET @WordCount = 0

WHILE @Index <= LEN(@InStr)
BEGIN
SET @Char = SUBSTRING(@InStr, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InStr, @Index - 1, 1)
END

IF @PrevChar = ' ' AND @Char != ' '
SET @WordCount = @WordCount + 1

SET @Index = @Index + 1
END

RETURN @WordCount
END
GO



now you can use that WordCount Function

DECLARE @String VARCHAR(4000)
SET @String = 'Hi this is Test line which count no of spaces.'
SELECT [dbo].[WordCount] ( @String )


Above Query will return Result 10 not any other,
hope you like this ,

SQL : how to sort ip Address.

ip address, is one of the key point to identify the user. now if your storing ip address on your database with the VARCHAR datatype with length of 25. guess you have 1 lakh's of records of different ip address and you want to sort that.

Then your sorting query will be

select [ipadd] as IpAddress from ip order by [ipadd]


now in this case it will return a result something like this


ipAddress
------------------------
10.0.0.1
192.165.20.2
255.205.2.3
255.145.53.6
64.35.88.9
68.4.5.99
69.65.236.235
98.125.85.36.0


something like that but actually its not your answer , it get sorted on first no. so becasue of that your result get warry.on actual basic the result should come in this format


ipAddress
------------------------
10.0.0.1
64.35.88.9
68.4.5.99
69.65.236.235
98.125.85.36.0
192.165.20.2
255.205.2.3
255.145.53.6


now how to achieve this use some technique here

select [IPAdd] from ip
order by CAST(PARSENAME([IPAdd], 4) AS INT),
CAST(PARSENAME([IPAdd], 3) AS INT),
CAST(PARSENAME([IPAdd], 2) AS INT),
CAST(PARSENAME([IPAdd], 1) AS INT),


now check this you get above result
thank you.

Export XML from SQL using a Stored Procedure.

1) Generate a file with the text-editor like:


<%begindetail%>
<%insert_data_here%>
<%enddetail%>


Save it as c:\temp\template.tpl

2) Open Query Analyzer and type:


sp_makewebtask @outputfile = 'c:\temp\myxmlfile.xml',
@query = 'select * from sysobjects for xml auto',
@templatefile = 'c:\temp\template.tpl'

The result is a XML-File!


More ^

Use of "IDENT_CURRENT"

in sql if we declare column as a Identity columen,as if we want to retrieve the last inserted value then use this
"IDENT_CURRENT"


All SQL Server functions that retrieve the last inserted value into the Identity column of a table - @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.


Use SCOPE_IDENTITY() , if you are not sure, if some triggers are going to be written or not on a table. Or, even better, use IDENT_CURRENT(tablename) to be more specific.

for more details

SQL Date Functions

here are some sql DATE related functions :

1. SET DATEFIRST 1 // set first day of the week as Monday

2. SELECT (datepart(ww,@tDate)) + 1- datepart(ww,dateadd(dd,-datepart(dd,@tDate),@tDate)) nWeek //Week number in a month for a specific date

3. SELECT DATEADD(mm, DATEDIFF(mm,0,@Month), 0) //First Day of the Month

4. SELECT DATEPART(WK, @Month) //Week Number of the Year

5. Select Current_Timestamp; // return current Select Current_Timestamp;

6. By using any of the following date functions it is possible to breakdown timestamps


SELECT MONTH(CURRENT_TIMESTAMP);- Return a Month(6)

SELECT DAY(CURRENT_TIMESTAMP);-Return a Day:(22)

SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)

SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840)

SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7)

SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31)

SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365)

SELECT MONTHNAME(CURRENT_TIMESTAMP); - returns the month name (January - December)

SELECT DAYNAME(CURRENT_TIMESTAMP); - returns the name of the day (Sunday - Saturday)

SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)