Finding stored procedure containing specific

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

SELECT OBJECT_NAME(id)
    FROM SYSCOMMENTS
    WHERE [text] LIKE '%Foo%'
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1
    GROUP BY OBJECT_NAME(id)

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

create rename do different things with excel workbook

Rename worksheet

You didn't spedify how do you access the excel file. However, example from here might be useful for you if you're using Microsoft.Office.Interop.Excel. Note that it opens first sheet in the file, line: (Worksheet)xlBook.Worksheets.get_Item(1)


using Excel = Microsoft.Office.Interop.Excel;

    object oMissing = System.Reflection.Missing.Value;
    Excel.ApplicationClass xl=new Excel.ApplicationClass();
        Excel.Workbook xlBook;
        Excel.Worksheet xlSheet;
        string laPath = Server.MapPath(@"\excel\xl_table.xls");
        xlBook = (Workbook)xl.Workbooks.Open(laPath,oMissing,
          oMissing,oMissing,oMissing ,oMissing,oMissing,oMissing
         ,oMissing,oMissing,oMissing,oMissing,oMissi ng,oMissing,oMissing);
        xlSheet = (Worksheet)xlBook.Worksheets.get_Item(1);
        xlSheet.Name = "CIAO";
        xlBook.Save();
        xl.Application.Workbooks.Close();


can try below also

 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Worksheet)xlApp.Worksheets["Sheet1"];
  worksheet.Name = “NewTabName”;


Add new worksheet after all worksheets
workbook.Sheets.Add(After: workbook.Sheets[workbook.Sheets.Count]);

Read all files from directory and delete specific files after sorting

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;


namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string folderPath = @"C:\myfiles\XML";
            List<Int64> fileName = new List<Int64>(); int i = 0;
            foreach (string file in Directory.EnumerateFiles(folderPath, "*.xml"))
            {
                string contents = File.ReadAllText(file);
                fileName.Add(Convert.ToInt64(Path.GetFileNameWithoutExtension(file)));
                Console.WriteLine((fileName[i]).ToString());
                i++;
            }
            Console.WriteLine("sorted order");
            fileName.Sort();
            Console.WriteLine("max value : " + fileName.Max());
            for (int j = 0; j < fileName.Count - 1; j++)
            {
                Console.WriteLine(fileName[j].ToString() + ": will be deleted");
                string file_with_path = folderPath + "\\" + fileName[j].ToString() + ".xml";
                Console.WriteLine(file_with_path + ": will be deleted");
                System.IO.File.Delete(file_with_path);
            }
            Console.ReadLine();
        }
    }
}

How many tables in database AdventureWorks have column name like ‘EmployeeID’?

SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_nameFROM sys.tables AS tINNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_IDWHERE c.name LIKE '%EmployeeID%'ORDER BY schema_name, table_name;

call scalar function in sql server

 select  dbo.fun_functional_score('01091400003') as [Result]

How to create Excel file from XML

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

namespace xml_to_excel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            DataSet ds = new DataSet();
            XmlReader xmlFile;
            int i = 0;
            int j = 0;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xmlFile = XmlReader.Create("product.xml", new XmlReaderSettings());
            ds.ReadXml(xmlFile);
            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
                    {
                        xlWorkSheet.Cells[1, k + 1] = ds.Tables[0].Columns[k].ColumnName;
                    }
                    xlWorkSheet.Cells[i + 2, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                }
            }
//make sure the below path exists C:\\access_db\\xml\\
            xlWorkBook.SaveAs("C:\\access_db\\xml\\product2.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlApp);
            releaseObject(xlWorkBook);
            releaseObject(xlWorkSheet);
            MessageBox.Show("Done .. ");
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

create a new xml file in bin folder of the application with name "product.xml" and
add below code

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<Table>
-<Product>
<Product_id>1</Product_id>
<Product_name>Product 1</Product_name>
<Product_price>1000</Product_price>
</Product>
-<Product>
<Product_id>2</Product_id>
<Product_name>Product 2</Product_name>
<Product_price>2000</Product_price>
</Product>
-<Product>
<Product_id>3</Product_id>
<Product_name>Product 3</Product_name>
<Product_price>3000</Product_price>
</Product>
-<Product>
<Product_id>4</Product_id>
<Product_name>Product 4</Product_name>
<Product_price>4000</Product_price>
</Product>
</Table>

Note:  make sure the below path exists  before you execute the appilcation
             C:\\access_db\\xml\\

SQL functions

http://www.c-sharpcorner.com/article/sql-server-functions-tips-and-tricks/