computed columns in sql server

CREATE TABLE dbo.MyTable

(

    Id int identity(100,1) NOT NULL PRIMARY KEY,

    CombinedId AS 'Adi-' + CAST(Id as varchar(16))

    Name varchar(50)

)

Or:

CREATE TABLE dbo.MyTable

(

    Id int NOT NULL PRIMARY KEY,

    PrefixField varchar(16),

    CombinedId AS PrefixField + CAST(Id as varchar(16))

)

Autocomplete extender in modal popup issue with solution

<cc1:AutoCompleteExtender ID="ace" runat="server" OnClientShown="ShowOptions">

</cc1:AutoCompleteExtender>

<script language="javascript" type="text/javascript">

    function ShowOptions(control, args) {

        control._completionListElement.style.zIndex = 10000001;

    }

</script>

Determining space used for each table in a SQL Server database

BEGIN try 

DECLARE @table_name VARCHAR(500) ; 

DECLARE @schema_name VARCHAR(500) ; 

DECLARE @tab1 TABLE(

        tablename VARCHAR (500) collate database_default

,       schemaname VARCHAR(500) collate database_default

); 

DECLARE  @temp_table TABLE (    

        tablename sysname

,       row_count INT

,       reserved VARCHAR(50) collate database_default

,       data VARCHAR(50) collate database_default

,       index_size VARCHAR(50) collate database_default

,       unused VARCHAR(50) collate database_default 

); 

INSERT INTO @tab1 

SELECT t1.name

,       t2.name 

FROM sys.tables t1 

INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

DECLARE c1 CURSOR FOR 

SELECT t2.name + '.' + t1.name  

FROM sys.tables t1 

INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

OPEN c1; 

FETCH NEXT FROM c1 INTO @table_name;

WHILE @@FETCH_STATUS = 0 

BEGIN  

        SET @table_name = REPLACE(@table_name, '[',''); 

        SET @table_name = REPLACE(@table_name, ']',''); 

        -- make sure the object exists before calling sp_spacedused

        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))

        BEGIN

                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;

        END

        

        FETCH NEXT FROM c1 INTO @table_name; 

END; 

CLOSE c1; 

DEALLOCATE c1; 

SELECT t1.*

,       t2.schemaname 

FROM @temp_table t1 

INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )

ORDER BY  schemaname,tablename;

END try 

BEGIN catch 

SELECT -100 AS l1

,       ERROR_NUMBER() AS tablename

,       ERROR_SEVERITY() AS row_count

,       ERROR_STATE() AS reserved

,       ERROR_MESSAGE() AS data

,       1 AS index_size, 1 AS unused, 1 AS schemaname 

END catch

Find and Replace Values in All Tables and All Text Columns in selected database

SET NOCOUNT ON

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 = 'adi'  -- find string

SET @stringToReplace = 'pavan' -- Replace string

  ------- adi replaced with pavan string

                       

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

     

WHILE @@FETCH_STATUS = 0

  BEGIN

    DECLARE COL_CURSOR CURSOR FOR

    SELECT A.NAME

    FROM   sys.columns A

           INNER JOIN sys.types B

             ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID

    WHERE  OBJECT_ID = @object_id

           AND IS_COMPUTED = 0

           AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')

    OPEN COL_CURSOR

    

    FETCH NEXT FROM COL_CURSOR

    INTO @columnName

    

    WHILE @@FETCH_STATUS = 0

      BEGIN

        SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName

                           + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),'''

                           + @stringToFind + ''',''' + @stringToReplace + ''')'

        

        SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''

        

        EXEC( @sqlCommand + @where)

        

        SET @count = @@ROWCOUNT

        

        IF @count > 0

          BEGIN

            PRINT @sqlCommand + @where

            PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)

            PRINT '----------------------------------------------------'

          END

        

        FETCH NEXT FROM COL_CURSOR

        INTO @columnName

      END

    

    CLOSE COL_CURSOR

    DEALLOCATE COL_CURSOR

    

    FETCH NEXT FROM TAB_CURSOR

    INTO @schema,

         @table,

         @object_id

  END

  

CLOSE TAB_CURSOR

DEALLOCATE TAB_CURSOR