SQL Tips and Tricks

Find Column Used in Stored Procedure – Search Stored Procedure for Column Name. Also Search Stored Procedure Code and Stored Procedure Text

SELECT obj.NAME SPName

,com.TEXT SPText

FROM sys.syscomments com

INNER JOIN sys.objects obj ON com.Id = obj.OBJECT_ID

WHERE com.TEXT LIKE ‘%Column Name%’

AND TYPE = ‘P’

Find Column Used in Tables – Search Tables for Column Name

SELECT COLUMN_NAME

,TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE ‘%Column Name%’

Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

SELECT DISTINCT obj.NAME

,obj.xtype

FROM syscomments com

INNER JOIN sysobjects obj ON com.id = obj.id

WHERE com.TEXT LIKE ‘%Table Name %’

JavaScriptSerializer set MaxJsonLength Property

I was working on WCF Service where I need to return large data in JSON format. We are using JavaScriptSerializer to perform operation. But we are facing following issue:

Error during serialization or deserialization using the JSON JavaScriptSerializer. The length of the string exceeds the value set on the maxJsonLength property.

While searching, I ended up with following solution:

The default maximum length for MaxJsonLength is 102400 characters.

The value of the MaxJsonLength property applies only to the internal JavaScriptSerializer instance that is used by the asynchronous communication layer to invoke Web services methods. (MSDN: ScriptingJsonSerializationSection.MaxJsonLength Property)

You can set the MaxJsonLength property on your web.config:

<configuration>
<system.web.extensions>
<scripting>
<webServices>
<jsonSerialization maxJsonLength=”2147483644″/>
</webServices>
</scripting>
</system.web.extensions>
</configuration>

If you are still receiving the error:

As a workaround, you can do the following within code

var serializer = new JavaScriptSerializer();

serializer.MaxJsonLength = Int32.MaxValue;

string jsonresult = serializer.Serialize(rowData);

Happy Coding 🙂