Sql server

Change Microsoft Sql Server Collation

Change Collation Name Below script generate alter table query for all tables of selected db, after executing below query and script then1. Right click on database 2. Select Properties3. Left hand side select option4. select your collation5. Press OK if any issue regrading collation depend on any function then generate a create query and delete it andContinue Reading “Change Microsoft Sql Server Collation”

Sql server

Split string table value function sql server

Split string table value function sql server Function return a table. @String : value with separator ex: 1,2,3@Delimiter : define separator as characterif pass blank string then return one row with null you can set default value. Function with sql server script:Copy the below script execute in database.

Sql server

drop all foreign key constraints in a database

drop all foreign key constraints in a database Using below code you can esaly remove all foreign-key constraints of tables. SELECT ‘IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N”[dbo].’ + FK +”’) AND parent_object_id = OBJECT_ID(N”[dbo].’ + PT + ”’)) ALTER TABLE ‘ + PT + ‘ NOCHECK CONSTRAINT ‘ + FK +Continue Reading “drop all foreign key constraints in a database”

Sql server

Insert Statement Script Generator Procedure

Insert statement generator for database CREATE PROC [dbo].[InsertGenerator] (@tableName varchar(100)) as –Declare a cursor to retrieve column specific information for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName OPEN cursCol DECLARE @string nvarchar(3000) –for storing the first half of INSERT statement DECLARE @stringData nvarchar(3000) –for storing theContinue Reading “Insert Statement Script Generator Procedure”

Sql server

Generate Http post Request From sql server

Generate Http post Request From sql server CREATE procedure HTTP_POST(  @sUrl varchar(200),  @response varchar(8000) out) As Declare @obj int ,@ hr int ,@status int , @msg varchar(255) exec @hr = sp_OACreate ‘MSXML2.ServerXMLHttp’, @obj OUT — exec @hr = sp_OACreate ‘MSXML2.ServerXMLHttp’, @obj OUT if @hr <> 0 begin Raiserror(‘sp_OACreate MSXML2.ServerXMLHttp.3.0 failed’, 16,1) return end exec @hrContinue Reading “Generate Http post Request From sql server”

Sql server

Db Cleaner

/* Drop all non-system stored procs */DECLARE @name VARCHAR(128)DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘P’ AND category = 0 ORDER BY [name]) WHILE @name is not nullBEGIN    SELECT @SQL = ‘DROP PROCEDURE [dbo].[‘ + RTRIM(@name) +’]’    EXEC (@SQL)    PRINT ‘Dropped Procedure: ‘ + @name    SELECT @nameContinue Reading “Db Cleaner”

Sql server

xp command shell to create file on server

xp command shell to create file on serverExec Master.dbo.Sp_Configure ‘Show Advanced Options’, 1 RECONFIGURE WITH OVERRIDE Exec Master.dbo.Sp_Configure ‘XP_CmdShell’, 1 RECONFIGURE WITH OVERRIDE exec master..xp_cmdshell ‘bcp select * from sys.tabels -o > c:\file.txt’ master..xp_cmdshell ‘bcp ..sys.objects out c:\file.sql -S -U -P -c ‘

Sql server

Store Procedure to get a Multiple Category

WITH CATEGORYVIEW (cat_Id, cat_ParentId,cat_orgname, cat_name,Main_catId,MainCatName ,cat_image,cat_displayorder,cat_IsShow) AS ( SELECT cat_Id, cat_ParentId,cat_name,cast(cat_name as varchar(255)),cast(cat_Id as varchar(255)),cast(cat_name as varchar(255)),cat_image,cat_displayorder,cat_IsShow FROM [CATEGORY_MST] WHERE isnull(cat_ParentId,0) = 0 UNION ALL SELECT C.cat_Id, C.cat_ParentId,c.cat_name ‘cat_orgname’, cast(CATEGORYVIEW.cat_name+’/’+C.cat_name as varchar(255)),cast(CATEGORYVIEW.cat_Id as varchar(255)),cast(CATEGORYVIEW.MainCatName as varchar(255)),C.cat_image,C.cat_displayorder,C.cat_IsShow FROM [CATEGORY_MST] C JOIN CATEGORYVIEW ON CATEGORYVIEW.cat_Id = C.cat_ParentId ) SELECT * FROM CATEGORYVIEW