Posts

Search entire SQL Server database and all tables for a specified string

-- ============================================= -- Author:        <Jainul Khan> -- Create date: <11-12-2014> -- Description: < Search entire SQL Server database and all tables for a specified string > -- ============================================= -- [dbo].[sp_SearchFromAllTables] 'Entered' CREATE PROCEDURE sp_SearchFromAllTables ( @SearchStr nvarchar ( 100 ) ) AS BEGIN DECLARE @Results TABLE ( ColumnName nvarchar ( 370 ), ColumnValue nvarchar ( 3630 )) SET NOCOUNT ON DECLARE @TableName nvarchar ( 256 ), @ColumnName nvarchar ( 128 ), @SearchStr2 nvarchar ( 110 ) SET   @TableName = '' SET @SearchStr2 = QUOTENAME ( '%' + @SearchStr + '%' , '''' ) WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN ( QUOTENAME ( TABLE_SCHEMA ) + '.' + QUOTENAME ( TABLE_NAME )) FROM INFORMATION_SCHEMA . TABLES WHERE        TABLE_TYPE = 'BASE TABLE' AND QUOTENAME ( TABLE_SCHEMA ) + '.' + QUOTENAME ( TABLE_NAME ) > @TableName AND OBJECTPROPERTY ( OBJECT_ID (

Read more →
0

Extended Date Formats in SQL Server

Here are some more date formats that does not come standard in SQL Server as part of the  CONVERT  function. Extended Date Formats Date Format SQL Statement Sample Output YY-MM-DD SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD] 99-01-24 YYYY-MM-DD SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD] 1999-01-24 MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] 08/99 MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005 YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08 YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]

Read more →
0

ASPxGridView - Enable Disable commandbuttons with conditions in AspxGridView

protected void grid1_CommandButtonInitialize( object sender, ASPxGridViewCommandButtonEventArgs e)         { if (e.VisibleIndex == -1) return ; if (e.ButtonType == ColumnCommandButtonType .Edit)             {                 e.Enabled = EditButtonVisibleCriteria(( ASPxGridView )sender, e.VisibleIndex);             }         } private bool EditButtonVisibleCriteria( ASPxGridView grid, int visibleIndex)         { object row = grid.GetRow(visibleIndex); return (( DataRowView )row)[ "Status" ].ToString().Contains( "Entered" );         }

Read more →
0

Sys.WebForms.PageRequestManagerParserErrorException: DevExpress

What's a PageRequestManagerParserErrorException? The UpdatePanel control uses asynchronous postbacks to control which parts of the page get rendered. It does this using a whole bunch of JavaScript on the client and a whole bunch of C# on the server. Asynchronous postbacks are exactly the same as regular postbacks except for one important thing: the rendering. Asynchronous postbacks go through the same life cycles events as regular pages (this is a question I get asked often). Only at the render phase do things get different. We capture the rendering of only the UpdatePanels that we care about and send it down to the client using a special format. In addition, we send out some other pieces of information, such as the page title, hidden form values, the form action URL, and lists of scripts. As I mentioned, this is rendered out using a special format that the JavaScript on the client can understand. If you mess with the format by rendering things outside of the render phase of the p…

Read more →
0

Find and Replace Values in All Tables and All Text Columns using SQL Server

DECLARE @stringToFind VARCHAR ( 100 ) DECLARE @stringToReplace VARCHAR ( 100 ) DECLARE @schema sysname DECLARE @table sysname DECLARE @count INT DECLARE @sqlCommand VARCHAR ( 8000 ) DECLARE @where VARCHAR ( 8000 ) DECLARE @columnName sysname DECLARE @object_id INT SET @stringToFind = 'Smith' SET @stringToReplace = 'Jones' DECLARE TAB_CURSOR CURSOR FOR SELECT    B . NAME      AS SCHEMANAME ,          A . NAME      AS TABLENAME ,          A . OBJECT_ID FROM sys . objects A INNER JOIN sys . schemas B ON A . SCHEMA_ID = B . SCHEMA_ID WHERE TYPE = 'U' ORDER BY 1 OPEN TAB_CURSOR FETCH NEXT FROM TAB_CURSOR INTO @schema ,      @table ,      @object_id

Read more →
0