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 :)