We a can get the list of all tables of a database in different ways : -
1.
SELECT * FROM sys.TABLES
2.
SELECT * FROM sysobjects WHERE TYPE='U'
3.
SELECT * FROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Popularity: 3% [?]
Posts Tagged ‘SQL’
How to Get The List Of All Tables in a Database – Sql Server 2005
June 22nd, 2009 
joy 
Posted in 
Tags: 
View Existing Triggers – Drop One or More Triggers in Sql Server 2005
June 19th, 2009 joy To view the existing triggers in the database
SELECT * FROM sys.triggers OR SELECT * FROM sysobjects WHERE xtype='TR'
To view the existing triggers in a specified table
sp_helptrigger tablename
To drop a trigger
DROP TRIGGER triggername
To drop more triggers at a time
DROP TRIGGER [trigger1],[trigger2],[trigger3]...[triggern]
Popularity: 9% [?]
Create backup copy of table in SQLServer – (Select Into)
December 29th, 2008 admin SELECT INTO statement can be used to create backup copy of a table.This sql statement selects records from one table according to the selection we make and inserts the result set into another new table with the table name we specify.
Syntax given below is used to make a exact copy of the table.That is it creates the table with the same no of columns and all records in the old table.
Syntax : Exact copy of old table
SELECT *
INTO new_table_name
FROM old_table_name
Example :
select * into employee_backup from employee
In this example a exact copy of the table employee is backed up as a new table employee_backup.
Syntax given below is used to make a new table with selected columns from old table.
Syntax : Selected columns from old table
SELECT column_name(s)
INTO new_table_name
FROM old_table_name
Example :
select employee_id,employee_name into employee_backup from employee
In this example a copy of the table employee with only columns “employee_id,employee_name” is backed up as a new table employee_backup.
Popularity: 9% [?]
Usage of Stored Procedures
December 16th, 2008 admin Reduce network traffic: You have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.
Caching query plan: The first time the stored procedure is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.
Ability to use output parameters: If you send inline SQL that returns one row, you can only get back a recordset. With stored procedures you can get them back as output parameters, which is considerably faster.
Permissions: When you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a stored procedure
Separation of logic: Remove the SQL-generating code and segregate it in the database.
Ability to edit without recompiling: This can be controversial. You can edit the SQL in a sproc without having to recompile the application.
Find where a table is used: With stored procedures, if you want to find all SQL statements referencing a particular table, you can export the stored procedure code and search it. This is much easier than trying to find it in code.
Optimization: It’s easier for a DBA to optimize the SQL and tune the database when stored procedures are used. It’s easier to find missing indexes and such.
SQL injection attacks: Properly written inline SQL can defend against attacks, but stored procedures are better for this protection.
Popularity: 2% [?]
Simple Add,Edit,Retrieve and Delete in Asp.net
December 9th, 2008 admin Here is a example application of Add,Edit,Retrieve and Delete operations using Asp.net. Download Creating a table employeeinfo for proceeding the operations. Sql Table Creation
CREATE TABLE employeeinfo ( empid int NOT NULL PRIMARY KEY, empname varchar(50), empaddress varchar(200), empsalary int )
Declaring Connection String and Variables
Namespace : using System.Data.SqlClient; string QryStr = ""; SqlConnection con = new SqlConnection("Server=servername;Integrated Security=true;Database=employee"); SqlCommand cmd; SqlDataReader dr;
In this example i have used windows authentication.You have to change the connection string according your specification. Adding Records
QryStr = "INSERT INTO employeeinfo VALUES('" +txtEmpId.Text + "','" + txtEmpName.Text + "','" + txtEmpAdd.Text + "','" + txtEmpSal.Text + "')"; con.Open(); cmd = new SqlCommand(QryStr, con); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); con.Close();
Updating Records
QryStr = "UPDATE employeeinfo SET empname = '"+txtEmpName.Text+"',empaddress = '"+txtEmpAdd.Text+"',empsalary = '"+txtEmpSal.Text+"' WHERE empid = '"+txtEmpId.Text+"'"; con.Open(); cmd = new SqlCommand(QryStr, con); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); con.Close();
Retrieve Records to textboxes Records will be Retrieved from the database and displayed in the text boxes when text change of employee id is fired.
QryStr = "SELECT * FROM employeeinfo WHERE empid = '" + txtEmpId.Text + "'"; con.Open(); cmd = new SqlCommand(QryStr, con); dr = cmd.ExecuteReader(); dr.Read(); if (dr.HasRows) { txtEmpName.Text = dr["empname"].ToString(); txtEmpAdd.Text = dr["empaddress"].ToString(); txtEmpSal.Text = dr["empsalary"].ToString(); } else { txtEmpName.Focus(); } con.Close();
Deleting Records
QryStr = "DELETE FROM employeeinfo WHERE empid = '" + txtEmpId.Text + "'"; con.Open(); cmd = new SqlCommand(QryStr, con); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); con.Close();
Popularity: 2% [?]
Create Database,Table and Procedure in Sql Server – Beginners
November 25th, 2008 admin Database Creation :
CREATE DATABASE EMPLOYEE
Table Creation :
CREATE TABLE EMPINFO
(
ROWID INT IDENTITY(1,1) NOT NULL,
EMPID INT NOT NULL,
EMPNAME VARCHAR(50) NOT NULL,
EMPDEPT VARCHAR(3) NOT NULL,
EMPSAL INT NOT NULL,
EMPADDRESS VARCHAR(100) NOT NULL,
ACTIVE CHAR(1) NOT NULL DEFAULT ‘T’
)
(
ROWID INT IDENTITY(1,1) NOT NULL,
EMPID INT NOT NULL,
EMPNAME VARCHAR(50) NOT NULL,
EMPDEPT VARCHAR(3) NOT NULL,
EMPSAL INT NOT NULL,
EMPADDRESS VARCHAR(100) NOT NULL,
ACTIVE CHAR(1) NOT NULL DEFAULT ‘T’
)
Procedure Creation :
CREATE PROC EMPINFO_ADD_EDIT
(
@iEMPID INT,
@vEMPNAME VARCHAR(50),
@vEMPDEPT VARCHAR(3),
@iEMPSAL INT ,
@vEMPADDRESS VARCHAR(100),
@cACTIVE CHAR(1)
)
AS
DECLARE @CNT INT;
BEGIN
SET @CNT = 0;
SELECT @CNT = COUNT(*) FROM EMPINFO WHERE EMPID = @iEMPID;
IF(@CNT = 0)
BEGIN
(
@iEMPID INT,
@vEMPNAME VARCHAR(50),
@vEMPDEPT VARCHAR(3),
@iEMPSAL INT ,
@vEMPADDRESS VARCHAR(100),
@cACTIVE CHAR(1)
)
AS
DECLARE @CNT INT;
BEGIN
SET @CNT = 0;
SELECT @CNT = COUNT(*) FROM EMPINFO WHERE EMPID = @iEMPID;
IF(@CNT = 0)
BEGIN
INSERT INTO EMPINFO(EMPID,EMPNAME,EMPDEPT,EMPSAL,EMPADDRESS,ACTIVE)VALUES @iEMPID,@vEMPNAME,@vEMPDEPT,@iEMPSAL,@vEMPADDRESS,@cACTIVE)
END
ELSE
BEGIN
UPDATE EMPINFO SET EMPNAME = @vEMPNAME,EMPDEPT = @vEMPDEPT,EMPSAL = @iEMPSAL, EMPADDRESS = @vEMPADDRESS,ACTIVE = @cACTIVE WHERE EMPID = @iEMPID
END
END
Popularity: 2% [?]
Create a Stored Procedure within a Stored Procedure
November 4th, 2008 admin When you want to create a stored procedure dynamically,we can create a stored procedure within a stored procedure.
CREATE PROC procCreateProcedure
(
@spname NVARCHAR(100)
)
AS
BEGIN
DECLARE @ProcedureName NVARCHAR(100)
DECLARE @Exe VARCHAR(8000)
(
@spname NVARCHAR(100)
)
AS
BEGIN
DECLARE @ProcedureName NVARCHAR(100)
DECLARE @Exe VARCHAR(8000)
/*setting the stored procedure name with spname passed*/
SET @ProcedureName = @spname
/*setting the stored procedure name with spname passed*/
/*Nothing will be done if procedure name is empty*/
IF LTRIM(RTRIM(@ProcedureName)) = ”
BEGIN
RETURN
END
/*Nothing will be done if procedure name is empty*/
/*Creating the stored procedure*/
SET @Exe = ‘CREATE PROC ‘ + @ProcedureName + CHAR(13) +
‘AS’ + CHAR(13) + CHAR(13) +
‘BEGIN’ + CHAR(13) +
‘SELECT * FROM EMPLOYEE’+ CHAR(13) +
‘END’
/*Creating the stored procedure*/
/*Executing the created stored procedure*/
EXEC(@Exe)
/*Executing the created stored procedure*/
END
/*Executing procCreateProcedure to creat new stored procedure*/
EXEC procCreateProcedure ‘procTest’
/*Executing procCreateProcedure to creat new stored procedure*/
now execute and see the stored procedure by name you passed.
EXEC procTest
Here in this example a new stored procedure with name ‘procTest’ which is passed in ‘procCreateProcedure’ is created and employee table is fectched when ‘procTest’ stored procedure is executed.
Popularity: 1% [?]
