Asp.net, MVC, C#,Jquery, Angularjs, WebAPI, HTML,Wcf, SQL: Sql

Showing posts with label Sql. Show all posts

8.8.10

find text in all sp of a particular sql database.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Reassign%'
AND ROUTINE_TYPE='PROCEDURE'

3.4.10

Search a particular text string in all SPs

http://www.sqlservercentral.com/Search/?q=search+text+in+sp&t=a&t=b&t=f&t=s
********************************************************

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Prod%'

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE Definition LIKE '%Prod%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

--nvarchar(4000)
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Prod%'
AND ROUTINE_TYPE = 'PROCEDURE'
********************************************************

Searching Column Name in All Tables

http://www.sqlservercentral.com/scripts/68998/

*********************************************
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%ColumnName%'

*********************************************

Find Text in all columns of all tables in a Database

http://www.sqlservercentral.com/scripts/Miscellaneous/65769/

**********************************************************************
DECLARE @TEXT VARCHAR(500)
SET @TEXT = 'DBAdmin'

DECLARE @TABLES TABLE([id] INT IDENTITY(1,1), TableName VARCHAR(500), ColumnName VARCHAR(500))
INSERT INTO @TABLES(TableName, ColumnName)
SELECT O.[NAME], C.[NAME]--SELECT *
FROM SYSOBJECTS O
JOIN SYSCOLUMNS C
ON C.ID = O.ID
WHERE O.XTYPE = 'U'
AND C.XTYPE NOT IN
(
127 --bigint
, 173 --binary
, 104 --bit
, 61 --datetime
, 106 --decimal
, 62 --float
, 34 --image
, 56 --int
, 60 --money
, 108 --numeric
, 59 --real
, 58 --smalldatetime
, 52 --smallint
, 122 --smallmoney
, 189 --timestamp
, 48 --tinyint
, 36 --uniqueidentifier
, 165 --varbinary
)
ORDER BY O.[NAME], C.[NAME]

IF EXISTS (SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#TMPREPORT%')
BEGIN
DROP TABLE #TMPREPORT
END
CREATE TABLE #TMPREPORT(COUNTER INT, TABLENAME VARCHAR(500), COLUMNNAME VARCHAR(500))

DECLARE @CNTR INT, @POS INT, @TableName VARCHAR(500), @ColumnName VARCHAR(500), @SQL VARCHAR(8000)
SELECT @POS = 1, @CNTR = MAX([ID]), @TableName = '', @ColumnName = ''
FROM @TABLES

--SELECT @POS, @CNTR, * FROM @TABLES

WHILE @POS <= @CNTR
BEGIN
SELECT @TableName = TableName, @ColumnName = ColumnName
FROM @TABLES
WHERE [ID] = @POS

SELECT @SQL = 'SELECT COUNT(*), ''' + @TABLENAME + ''' [TABLE],''' + @COLUMNNAME + '''[COLUMN] FROM ' + @TableName + ' WHERE CAST(' + @ColumnName + ' AS VARCHAR) LIKE ''%' + @TEXT + '%'''
--PRINT @SQL
BEGIN TRY
INSERT INTO #TMPREPORT(COUNTER, TABLENAME, COLUMNNAME)
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT @@ERROR
PRINT @SQL
END CATCH
SELECT @POS = @POS + 1
END

SELECT * FROM #TMPREPORT WHERE COUNTER > 0
DROP TABLE #TMPREPORT
----------------------------------------------------------------------------------------
/*127 : bigint
173' --binary
104' --bit
175' --char
61' --datetime
106' --decimal
62' --float
34' --image
56' --int
60' --money
239' --nchar
99' --ntext
108' --numeric
231' --nvarchar
59' --real
58' --smalldatetime
52' --smallint
122' --smallmoney
98' --sql_variant
231' --sysname
35' --text
189' --timestamp
48' --tinyint
36' --uniqueidentifier
165' --varbinary
167' --varchar
*/
**********************************************************************

With Sql Generate Parameters for store procedure

******************************************************************************
select '@' + LOWER(substring(column_name,1,1)) + substring(column_name,2,Len(column_name)) + ' ' + UPPER(Data_Type) + CASE WHEN character_maximum_length is null THEN ',' ELSE ' (' + CAST(character_maximum_length as VARCHAR(200))+ '),' END from Information_Schema.columns
where Table_Name ='studentbatchResult'
*******************************************************************************
select 'db.AddInParameter(command, "@' +
LOWER(substring(column_name,1,1)) + substring(column_name,2,Len(column_name)) + '", DbType.' +
CASE WHEN Data_Type = 'VARCHAR' THEN 'String' WHEN Data_Type = 'int' THEN 'Int32'
WHEN Data_Type = 'DateTime' Then 'DateTime' WHEN Data_Type = 'bit' THEN 'Boolean' WHEN Data_Type = 'text' THEN 'String' END + ', objUser.' + column_Name + ');' from Information_Schema.columns where Table_Name ='tbl_UserDetails'
********************************************************************************

with Sql Generate DAL Parameters

***********************************************************************
select 'if (string.IsNullOrEmpty(myBatchFileResult.' + substring(column_name,1,Len(column_name))+')){
myCommand.Parameters.AddWithValue("@' + substring(column_name,1,Len(column_name)) +'", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@' + substring(column_name,1,Len(column_name))+'", myBatchFileResult.'+ substring(column_name,1,Len(column_name)) +');

}'

from Information_Schema.columns where Table_Name ='BatchUploadResult'
***************************************************

select 'if (string.IsNullOrEmpty(myBatchFileResult.' + substring(column_name,1,Len(column_name))+')){
myCommand.Parameters.AddWithValue("@' + substring(column_name,1,Len(column_name)) +'", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@' + substring(column_name,1,Len(column_name))+'", myBatchFileResult.'+ substring(column_name,1,Len(column_name)) +');

}'

from Information_Schema.columns where Table_Name ='StudentBatchResult'
*************************************************************************

With Sql Generate C# class code for table

http://www.sqlservercentral.com/scripts/Development/61477/

NOTE:first signup, if you are not registered with this website

*************************************************
CREATE PROCEDURE usp_TableToClass
/*
Created by Cade Bryant.
Generates C# class code for a table
and fields/properties for each column.

Run as "Results to Text" or "Results to File" (not Grid)

Example: EXEC usp_TableToClass 'MyTable'
*/

@table_name SYSNAME

AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
sort INT,
code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{'
+ CHAR(13) + CHAR(10) + CHAR(9) + '}'

INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + '_' + COLUMN_NAME + ';' + CHAR(9)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 9, CHAR(9) + 'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + COLUMN_NAME +
CHAR(13) + CHAR(10) + CHAR(9) + '{' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get { return _' + COLUMN_NAME + '; }' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set { _' + COLUMN_NAME + ' = value; }' +
CHAR(13) + CHAR(10) + CHAR(9) + '}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#endregion' +
CHAR(13) + CHAR(10) + '}'

SELECT code FROM @temp
ORDER BY sort

*************************************************

30.3.10

MAX and MIN of Datetime

SELECT Id, Min(Time) as MinTime, Max(Time) as MaxTime FROM Frames GROUP
by Id

14.3.10

Instant SQl formater

Instant SQl formater
http://www.dpriver.com/pp/sqlformat.htm

write sql fast
http://www.sqlservercentral.com/articles/Product+Reviews/3050/

sql performance

http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx

Convert SQL Server 2008 to SQL Server 2005

1.)Convert SQL Server 2008 to SQL Server 2005

http://www.hyper-v-mart.com/HowTo/Convert_SQL_Server_2008_to_SQL_Server_2005.aspx

2.)With data:

1) Right click the database in sql 2008 which you want to do with
2) Choose Task and Generate script
3) Make sure select "script all objects in the database"
4) In the Script Option, choose the item Script for SQL server version to SQL2005
and Script data to be true! Those two are the key steps!
5) Here you go to check the generated script. You should find out insert statement for inserting the data

3.)Debugging Stored Procedures in Visual Studio 2005
http://www.sqlteam.com/article/debugging-stored-procedures-in-visual-studio-2005

4.)Call Stored procedure from within another stored procedure, return values
http://www.aspfree.com/c/a/ASP.NET-Code/Call-Stored-procedure-from-within-another-stored-procedure-return-values/

5.) Differences between varchar and nvarchar in SQL Server

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1266201,00.html

6.) Calling functions in sql.
http://www.functionx.com/sqlserver/functions/create.htm

25.2.10

Sql Functions

NOLOCK

http://www.sqlservercentral.com/Forums/Topic282321-149-1.aspx
http://www.sqlservercentral.com/articles/Performance+Tuning/2764/

http://www.1keydata.com/sql/sql-use.html

SQL WILD CARDS:
http://www.techonthenet.com/sql/like.php
http://www.w3schools.com/SQL/sql_wildcards.asp

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to
COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

The DROP command removes a table from the database. All the tables' rows,
indexes and privileges will also be removed. The operation cannot be rolled back.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
********************************************************
TURN IMPLICIT TRANSACTION ON IN SQL:

http://blog.techdreams.org/2007/11/implicit-transactions-onoff-sql-server.html

http://www.allinterview.com/showanswers/71598.html

18.2.10

Database Mirroring in SQL Server 2008

1.)Database Mirroring in SQL Server 2008

http://www.databasejournal.com/features/mssql/article.php/3828341/Database-Mirroring-in-SQL-Server-2008.htm

****************************************************************
2.)Replication in SQL Server 2008

http://www.databasejournal.com/features/mssql/article.php/3820361/Peer-to-Peer-Replication-in-SQL-Server-2008--Add-a-node-and-resolve-conflict.htm

*****************************************************************
3.)get day from datetime
DATENAME ( datepart ,date )
http://msdn.microsoft.com/en-us/library/ms174395(SQL.90).aspx

******************************************************************
4.) Delete temp data from pc shortcut
run type:%temp%

Red gate for sql

http://www.red-gate.com/

28.1.10

sql website

http://www.sqlservercentral.com/Articles/Video

14.1.10

EmailMail Query

GO
/****** Object: StoredProcedure [dbo].[Mail]
10:16:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Usp_MailHeadersGetAll]
@MessageType Char(3)='INB'
,@User_FK BIGINT=0
,@Email_FK BIGINT=0
,@datefrom varchar(15)=''
,@dateto varchar(15)=''
,@PageIndex int=1
,@RowsPerPage int=99999
,@HasAttachments Bit=0
,@IsRead bit=0
,@IsUnread bit =0
,@OrderBY smallint=4
,@Order smallint=2

as
BEGIN

Declare @strpaging as nvarchar(4000)
Declare @strqry as nvarchar(4000)

if(len(@MessageType)=3)
BEGIN

--PagingData
set @strpaging = 'SELECT count(header_pk) as ''TotalRecords'''
set @strpaging = @strpaging + ' FROM crm_mail_headers (nolock) INNER JOIN'
set @strpaging = @strpaging + ' crm_Mail_Action (nolock) ON crm_mail_headers.action_fk = crm_Mail_Action.action_pk '
set @strpaging = @strpaging + ' where crm_mail_headers.User_FK ='+ CAST(@User_FK as varchar) +' and crm_mail_headers.Email_FK ='+ CAST(@Email_FK as varchar)
if(@HasAttachments=1)
set @strpaging = @strpaging + ' and crm_mail_headers.HasAttachment =1 '
if(@IsRead=1)
set @strpaging = @strpaging + ' and crm_mail_headers.IsRead =1 '
if(@IsUnread=1)
set @strpaging = @strpaging + ' and crm_mail_headers.IsRead =0 '

if(@MessageType='INB')
set @strpaging = @strpaging + ' and crm_mail_headers.IsInbox =1 '
if(@MessageType='SNT')
set @strpaging = @strpaging + ' and crm_mail_headers.IsSent =1 '
if(@MessageType='DRA')
set @strpaging = @strpaging + ' and crm_mail_headers.IsDraft =1 '
if(@MessageType='SPA')
set @strpaging = @strpaging + ' and crm_mail_headers.IsSpam =1 '
if(@MessageType='TRA')
set @strpaging = @strpaging + ' and crm_mail_headers.IsDeleted =1 '

--ListData

set @strqry = 'SELECT * FROM '
set @strqry = @strqry + ' ( '
set @strqry = @strqry + ' SELECT ROW_NUMBER() OVER(ORDER BY header_pk desc) as RowNum ,header_pk,user_Fk,UniqueId,ReceivedDate,MessageId,HasAttachment,IsBodySaved,IsRead,ActionComment,'
set @strqry = @strqry + ' MessageFrom As Sender,Subject as Sub,action_fk,'
set @strqry = @strqry + ' Case IsRead When 0 then ''Images/new.jpg'' else ''Images/old.gif'' ENd as ''EMailStatus'','
set @strqry = @strqry + ' Case IsRead When 0 then ''''+MessageFrom+'''' else MessageFrom END as ''MessageFrom'','
set @strqry = @strqry + ' Case IsRead When 0 then ''''+isnull(MessageTo,'''')+'''' else isnull(MessageTo,'''') END as ''MessageTo'','
set @strqry = @strqry + ' Case HasAttachment When 1 then ''

'' else '' '' ENd as ''AttchmentStatus'','
set @strqry = @strqry + ' Case IsRead When 0 then ''''+Subject+'''' else Subject END as ''Subject'','
set @strqry = @strqry + ' Case IsRead When 0 then ''''+Action+'''' else Action END as ''Action'''

set @strqry = @strqry + ' FROM crm_mail_headers (nolock) INNER JOIN'
set @strqry = @strqry + ' crm_Mail_Action (nolock) ON crm_mail_headers.action_fk = crm_Mail_Action.action_pk '
set @strqry = @strqry + ' where crm_mail_headers.User_FK ='+ CAST(@User_FK as varchar) +' and crm_mail_headers.Email_FK ='+ CAST(@Email_FK as varchar)
if(@HasAttachments=1)
set @strqry = @strqry + ' and crm_mail_headers.HasAttachment =1 '
if(@IsRead=1)
set @strqry = @strqry + ' and crm_mail_headers.IsRead =1 '
if(@IsUnread=1)
set @strqry = @strqry + ' and crm_mail_headers.IsRead =0 '

if(@MessageType='INB')
set @strqry = @strqry + ' and crm_mail_headers.IsInbox =1 '
if(@MessageType='SNT')
set @strqry = @strqry + ' and crm_mail_headers.IsSent =1 '
if(@MessageType='DRA')
set @strqry = @strqry + ' and crm_mail_headers.IsDraft =1 '
if(@MessageType='SPA')
set @strqry = @strqry + ' and crm_mail_headers.IsSpam =1 '
if(@MessageType='TRA')
set @strqry = @strqry + ' and crm_mail_headers.IsDeleted =1 '

set @strqry = @strqry + ' ) as Mails'

set @strqry = @strqry + ' WHERE RowNum BETWEEN '+ cast(@PageIndex as varchar(100)) +' AND '+ cast( ((@PageIndex + @RowsPerPage) - 1) as varchar(100))
--Order By [Field]
if(@OrderBY=1)
set @strqry = @strqry + ' Order By ReceivedDate '
else if(@OrderBY=2)
set @strqry = @strqry + ' Order By Sender '
else if(@OrderBY=3)
set @strqry = @strqry + ' Order By Sub '
else if(@OrderBY=4)
set @strqry = @strqry + ' Order By ReceivedDate '
else if(@OrderBY=5)
set @strqry = @strqry + ' Order By Action '
else
set @strqry = @strqry + ' Order By header_pk '

--- OrderBy

if(@Order=1)
set @strqry = @strqry + ' ASC'
if(@Order=2)
set @strqry = @strqry + ' DESC'

END

EXECUTE sp_executesql @strpaging
EXECUTE sp_executesql @strqry
-- select @strqry

END

30.6.08

Insert and Update Query

IF EXISTS(
SELECT 1
FROM MY_TABLE
WHERE ITEM='somevalue' AND ENTERDATE='12/31/1999')
--Update Statement
UPDATE MY_TABLE
SET ITEM='anothervalue'
WHERE ITEM='somevalue' AND ENTERDATE='12/31/1999'
ELSE
--Insert Statement
INSERT INTO MY_TABLE
(ITEM, ENTERDATE)
VALUES
('somevalue', '12/31/1999')