Day to day need in Sql

1. Find SP containing text

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
AND ROUTINE_TYPE='PROCEDURE'

2. Find table with given column and it's length

SELECT Column_Name, Table_Name, Col_Length(Table_Name,Column_Name)
FROM INFORMATION_SCHEMA.Columns 
WHERE Column_Name LIKE '%Name%' 

3. Find SP containing text
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
AND ROUTINE_TYPE='PROCEDURE'

4. Change column size throughout DB with given Column Name
DEALLOCATE My_Cursor
DECLARE @COLUMN_NAME VARCHAR(100)
DECLARE @TABLE_NAME VARCHAR(100)
DECLARE @tabLen VARCHAR(100)
Declare @sql as VARCHAR(500)=''
DECLARE My_Cursor CURSOR  DYNAMIC OPTIMISTIC FOR
SELECT COLUMN_NAME,
 TABLE_NAME  , COL_LENGTH ( TABLE_NAME , COLUMN_NAME ) As tabLen
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%TIN'
and COL_LENGTH ( TABLE_NAME , COLUMN_NAME )>-1
AND COL_LENGTH ( TABLE_NAME , COLUMN_NAME )<20
AND DATA_TYPE = 'varchar' 

OPEN  My_Cursor
FETCH NEXT FROM My_Cursor INTO @COLUMN_NAME,@TABLE_NAME,@tabLen
WHILE @@FETCH_STATUS <> -1
 BEGIN
  
 SET @sql=''
 SET @sql= 'ALTER TABLE '+@TABLE_NAME +
' ALTER COLUMN '+@COLUMN_NAME+' VARCHAR(20)'
 select @sql
 EXEC 
 ( @sql)
 
  
  
  
FETCH NEXT FROM My_Cursor INTO 
 @COLUMN_NAME,@TABLE_NAME,@tabLen
END




sqlcmd -S <server Name> -U sa -P sapassword -i inputquery_file_name -o outputfile_name