Archive

Posts Tagged ‘MSSQL’

MSSQL useful tips

March 13, 2012 Leave a comment
  • MSSQL set null when value is not numeric in select query
SELECT CASE ISNUMERIC(column1) WHEN 1 THEN column1 ELSE NULL END FROM table1
  • SQL Server Error Messages – Msg 8152 – String or binary data would be truncated.  The statement has been terminated.

This error message is occurred when I tried to insert data to values from another table.It’s due to one column in the table that im inserting data have lesser length than other table column.(varchar(25) – varchar(max)).It couldn’t found previously because second table column I have used max length but when inserting data it filled with strings that contains more than 25 characters.

I found another command to skip that error and continue but its like a try catch statement without catch part(thanks yasindu bro for nice example).

SET ANSI_WARNINGS OFF

Need to use above command before inserting records.but sometimes it’s not working

  • Inserting multiple records to a table

This isn’t simple thing but I previously work with MySQL and that is different and we can’t use

INSERT INTO tblName (column1,column2)
VALUES
('Value1','value11'),
('Value2','value22'),
('Value3','value33')
INSERT INTO tblName (column1,column2)
select ‘Value1′,’Value11’
UNION ALL
select ‘Value2′,’Value22’
UNION ALL
select ‘Value3′,’Value33’
Advertisements
Categories: Issues, Programming, TSQL Tags: , ,

How to find a specific string from stored procedures, triggers and functions in database

January 12, 2012 Leave a comment

There are several tables and views containing stored procedures, functions and triggers definitions

Views – SYS.SYSCOMMENTS, SYS.SQL_MODULES

Tables – INFORMATION_SCHEMA.ROUTINES

I recommend following query it shows all SPs and Fns that contains any specific text.If we want to search only procedure then  filter in where clause “AND obj.Type=’P’”

SELECT DISTINCT obj.name AS Object_Name,obj.type_desc 
FROM sys.sql_modules sm INNER JOIN sys.objects obj ON sm.object_id=obj.object_id 
WHERE sm.definition Like ‘%search text%’

If we want to search specific function or objects names we can find dependencies using following system stored procedure

EXEC sp_depends @objname =searchobjectname

There is another way to find specific text using INFORMATION_SCHEMA.ROUTINES. But for me it didn’t give me all the results.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE
‘%search text%’

AND ROUTINE_TYPE=’PROCEDURE’ (for procedures only)

And  also SYS.SYSCOMMENTS view can use to same usage but I got duplicates from some results.

SELECT SO.NAME, SC.TEXT
FROM SYS.SYSOBJECTS SO

JOIN SYS.SYSCOMMENTS SC ON SO.ID = SC.ID

WHERE SC.TEXT LIKE ‘%search text%’
–SO.TYPE = ‘P'(for procedures)

Hope this post helps you.Feel free to give your suggestion on this post.Until next post cheers..!!!

Categories: TSQL Tags: , ,