Comandos Sql Server

Comandos Sql Server

-----**************************************************
-----**********COLUNAS*** ********** ********** ******
-----**************************************************
     exec  sp_columns 'XXXXXXXXX'
-----**************************************************
-----**********sp_password ********** ********** ******
-----***** *********************************************
exec sp_password NULL,'','SA'

--Adiciona ou altera a senha de um login do SQL Server. Os argumentos so: senha atual, nova senha e login. Para um novo usurio senha atual deve ser nulo

-----**************************************************
-----**********INFORMA TODOS OS PROCESSOS **********
-----**************************************************
select * from master..sysprocesses where dbid = db_id ('XXXXXXXXX')

-----**************************************************
-----********** --SP-- *****************************
-----**********
****************************************
sp_helpdb
sp_help
sp_columns
sp_who

exec Sp_helptext 'PROCEDURE'
SELECT * FROM syscomments --> Listar Procedures.

-----**************************************************
-----********** --SHRINKDATABASE-- *****************
-----**************************************************
 DBCC SHRINKDATABASE
 (Nome_BancodeDados ,porcentagem_final
[, {NOTRUNCATE | TRUNCATEONLY}] )
---**************************************************
-----****** --Restore Full e Diferencial-- *********
-----**************************************************
Use master
ALTER DATABASE XXXXXX
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE--ALTER DATABASE XXXXXX SET RECOVERY SIMPLE;
--Full
RESTORE
DATABASE XXXXXX
FROMDISK = 'XXXXXX'
WITH NORECOVERY
--Diferencial

RESTORE DATABASE XXXXXX FROM DISK = 'XXXXXX' WITH  RECOVERY

-----**************************************************
-----****** --Derrubar usurios-- *********
-----****** *******************************************USE MASTER
ALTER DATABASE XXXXXX
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE XXXXXX
SET MULTI_USER WITH NO_WAIT
ALTER DATABASE XXXXXX SET OFFLINE WITH NO_WAIT
 

Att,
Leonardo Rocha.

Função para Formartar Dinheiro - Sql Server

Função para Formartar Dinheiro - Sql Server


CREATEFUNCTION FormatDinheiro(@Valor DECIMAL(20, 2))
RETURNSVARCHAR(30)
ASBEGIN
DECLARE @ValorStr VARCHAR(30), @Inteiro VARCHAR(30), @Decimal VARCHAR(3), @I INT,@Count,INT,@IntLen INT
SET @ValorStr = CONVERT(VARCHAR(30), @Valor)SET @ValorStr = RTRIM(LTRIM(REPLACE(@ValorStr, '.', '')))
SET @ValorStr = REPLACE(@ValorStr, ',', '')
SET @Inteiro = ''IF (Len(@ValorStr) = 1)
BEGINSET @Inteiro = '0'
SET @Decimal = '0'+@ValorStr
END ELSE
BEGIN
IF (Len(@ValorStr) = 2)
BEGIN
SET @Inteiro = '0'
SET @Decimal = @ValorStr
END ELSE
BEGIN
SET @Decimal = Substring(@ValorStr, (Len(@ValorStr)-1), Len(@ValorStr))
SET @I = 3 SET @Count = 0WHILE
(@I <= Len(@ValorStr))BEGIN
IF  (@Count = 3)
BEGIN
SET @Inteiro = '.'+@Inteiro
SET @Count = 0
END

  SET @IntLen = (Len(@ValorStr)+1)-@I
IF (@IntLen >= 0)
BEGIN
SET @Inteiro = Substring(@ValorStr, @IntLen, 1)+@Inteiro
END
SET @I = @I + 1
SET @Count = @Count + 1
END
END
END
IF  (@Inteiro = '') SET @Inteiro = '0'
IF (@Decimal = '') SET @Decimal = '00'
RETURN@Inteiro+','+@Decimal
END



Att,
Leonardo Rocha

Métodos de Acesso à Dados.

Métodos de Acesso à Dados.

        /// <summary>

        /// Retorna um Dataset

        /// </summary>

        /// <param name="p_sql">String Sql</param>

        /// <param name="p_dataset_name">Nome do dataset a ser retornado</param>

        /// <returns>Dataset</returns>

        public static DataSet GetDataSet(string p_sql, string p_dataset_name) {

            DataSet ds = new DataSet();

            try {

                SqlConnection cn = new SqlConnection(p_strcn);

                SqlDataAdapter da = new SqlDataAdapter(p_sql, cn);

                da.Fill(ds, p_dataset_name);

                return ds;

            }

            catch (Exception ex) {  

                throw new Exception("Erro ao buscar dados.");

            }

        }

         /// <summary>

        /// Executa uma instrução que sem retorno de resultado

        /// </summary>

        /// <param name="p_sql">String Sql</param>

        /// <returns>Int32</returns>

        public static Int32 ExecuteNonQuery(string p_sql) {

            Int32 iReturn;

            SqlConnection cn = new SqlConnection(p_strcn);

            SqlCommand cm = new SqlCommand(p_sql, cn);

            cm.CommandTimeout = 900;

            try {

                cn.Open();

                iReturn = cm.ExecuteNonQuery();

            }

            catch (Exception ex) {    

                throw new Exception("Erro ao executar transação de dados.");

            }

            finally {

                cn.Close();

                cn.Dispose();

                cm.Dispose();

            }

            return iReturn;

        }

        /// <summary>

        /// Executa uma instrução que retorna um vetor de uma coluna e uma linha.

        /// </summary>

        /// <param name="p_sql">String Sql</param>

        /// <returns>String</returns>

        public static string ExecuteScalar(string p_sql) {

            string sReturn;

            SqlConnection cn = new SqlConnection(p_strcn);

            SqlCommand cm = new SqlCommand(p_sql, cn);

            cm.CommandTimeout = 900;

            try {

                cn.Open();

                sReturn = cm.ExecuteScalar().ToString();

            }

            catch (Exception ex) {  

                throw new Exception("Erro ao executar buscar array unico.");

            }

            finally {

                cn.Close();

                cn.Dispose();

                cm.Dispose();

            }

            return sReturn;

        }

    }

}

By,
Marcelo Moreto.

WPF - Combobox

public void GetDropTpBase() {
SqlConnection sqlCon = new SqlConnection("Data Source=localhost;Initial Catalog=WSsia;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlCon;
cmd.CommandType =CommandType.Text;cmd.CommandText = "select id_tpbase, desc_tpbase from tb_tpbase";SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = cmd;

DataSet ds = new DataSet();  
try {sqlDa.Fill(ds, "tb_tpbase");
 comboBoxTPBase.DataContext = ds.Tables["tb_tpbase"].DefaultView;

comboBoxTPBase.DisplayMemberPath = ds.Tables["tb_tpbase"].Columns["desc_tpbase"].ToString();comboBoxTPBase.SelectedValuePath = ds.Tables["tb_tpbase"].Columns["id_tpbase"].ToString();

}

catch (Exception ex) {   

MessageBox.Show("Ocorreu um erro ao executar o tipo de base!");            }



finally {

sqlDa.Dispose();

cmd.Dispose();sqlCon.Dispose();

}

}
        


Att,
Leonardo Rocha.