• About Ken
  • Ken’s CV

All About SharePoint

~ Liedong(Ken) Zheng, Independent SharePoint Consultant

All About SharePoint

Tag Archives: TSQL

Useful Commands To Search Columns or Stored Procedures

19 Tuesday Jan 2010

Posted by ken zheng in SQL

≈ Leave a comment

Tags

stored procedure, TSQL

***************************************************
* search for a column name
***************************************************
SELECT sysobjects.name as “Table”,
syscolumns.name as “Column”
from sysobjects , syscolumns
where sysobjects.id = syscolumns.id
and sysobjects.xtype = ‘u’
and syscolumns.name like ‘%id%’
order by sysobjects.name, syscolumns.name

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

***************************************************
* search for a stored procedure
***************************************************
SELECT B.name AS ‘Schema’, A.name AS ‘Procedure Name’
FROM sys.sysobjects A join sys.schemas B
ON B.schema_id = A.uid
WHERE xtype=’P’ AND A.name LIKE ‘%Products%’
****************************************************

***************************************************
* find a stored procedure containing
***************************************************
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%foobar%’
AND ROUTINE_TYPE=’PROCEDURE’
**************************************************

Advertisements

TSQL Function: Convert Varchar To Number

10 Friday Oct 2008

Posted by ken zheng in SQL

≈ 1 Comment

Tags

TSQL

Below is the function created by Damian Edwards to strips out non-numeric characters, after doing that I can convert to numeric safely (after checking for empty strings and nulls of course):

— Function to strip out non-numeric chars
ALTER FUNCTION dbo.UDF_ParseNumericChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
–SET @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @string)
SET @IncorrectCharLoc = PATINDEX(‘%[^0-9.]%’, @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
SET @IncorrectCharLoc = PATINDEX(‘%[^0-9.]%’, @string)
END
SET @string = @string
RETURN @string
END
GO

Pages

  • About Ken
  • Ken’s CV

Blog Stats

  • 1,607,340 hits

.Net 3.5 5566 AJAX BCS BDC C# Calendar Content Type CSS Custom Page Document ID Fast Search favicon Feature IIS InfoPath infopath form JavaScipt JavaScript jQuery LINQ Listview Managed Property Master Page MCP MCPD migration MOQ MySite Page Layout PowerShell Proxy Search Search Scope SharePoiint 2010 SharePoiint 2010; Search SharePoin Sharepoint Sharepoint 2007 SharePoint2007 Sharepoint 2010 Sharepoint Search Silverlight Site Collection Site Definition Site Master Page site templates SPDisposeCheck SPD workflow SPSiteDataQuery SPUser SQL SSP stored procedure STSADM survey Tab TechEd Tech ED TSQL User Profile User Profiles Synchronization Service;SharePoint 2010 vs 2008 vs 2010 vs2010 WCF web.config webpart Web Part Web Service windbg workflow XML XSL XSLT

Archives

  • March 2016
  • April 2014
  • March 2014
  • February 2014
  • December 2013
  • October 2013
  • September 2013
  • March 2013
  • February 2013
  • August 2012
  • June 2012
  • May 2012
  • April 2012
  • February 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • October 2010
  • September 2010
  • August 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • February 2010
  • January 2010
  • December 2009
  • November 2009
  • October 2009
  • September 2009
  • August 2009
  • July 2009
  • June 2009
  • May 2009
  • April 2009
  • March 2009
  • February 2009
  • January 2009
  • December 2008
  • November 2008
  • October 2008
  • September 2008
  • August 2008
  • July 2008
  • June 2008
  • May 2008
  • April 2008
  • March 2008
  • February 2008
  • January 2008

Twitter Updates

  • @Nintex just wonder if there is any SharePoint online workflow server issue right now? All workflows are not reachable 7 months ago
  • @yarratrams Hi My boy lost his Camberwell Grammar Balzer on Tram 75 yesterday 16/05/17 around 4:30. He got off at Warrigal road, 11 months ago
  • SharePoint Online Workflow Service Failed Today! 12 months ago
  • Follow the latest news on the Tour #TDF2016 bit.ly/29TNYBl 1 year ago
  • @katushacycling @Kristoff87 1 year ago
Locations of visitors to this page

Blogroll

  • E-books Library
  • My Linkedin
  • SharePoint Community
  • SharePoint Developer Center
  • Silverlight Show
April 2018
M T W T F S S
« Mar    
 1
2345678
9101112131415
16171819202122
23242526272829
30  
Advertisements

Blog at WordPress.com.