sql查看数据库存储过程内容

1.

 

 /// <summary>
        /// 获取存储过程定义
        /// </summary>
        /// <param name="proc"></param>
        /// <returns></returns>
        /// <remarks>存储过程内容可能存在于多行中，所以读取的时候应遍历所表所有行</remarks>
        public static DataTable GetProcContent(string database, string proc)
        {
            string strCmd = "sp_helptext";
            try
            {
                string connString = DbHelperSQL.connectionString;
                int start = connString.IndexOf("database=") + 9;
                int length = connString.IndexOf(";", connString.IndexOf("database=")) - start;
                string old = connString.Substring(start, length);
                DbHelperSQLP helper = null;

                connString = connString.Replace(old, database);
                helper = new DbHelperSQLP(connString);

                System.Data.SqlClient.SqlParameter[] parameters = {
                                                                      new System.Data.SqlClient.SqlParameter("@objname",SqlDbType.VarChar,80)
                                                                   };
                parameters[0].Value=proc;
                DataSet ds = helper.RunProcedure(strCmd,parameters,"ProcContent");
                
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }

 

2.sql语句

 

select ROUTINE_SCHEMA,
    ROUTINE_NAME,
    ROUTINE_DEFINITION 
    from INFORMATION_SCHEMA.ROUTINES 
    where ROUTINE_TYPE='PROCEDURE' 
    and routine_name='proc';
--proc：存储过程名称，ROUTINE_DEFINITION：存储过程定义

 

3.sql语句

 

select text 
    from syscomments 
    where id = (select id from sysobjects where xtype='p' and name = @procName)
--procName:存储过程名称