|
|
-
|
|
The symbol @ is mainly used to identify a variable, parameter and table variable. The usage of @ sometimes make you to have some fun. Here are some methods
You can declare a table variable with only @s. Cosider this example
declare @@@ table(i int )
insert into @@@(i) select 345
select i from ......
|
|
-
|
|
The following methods can be used to know the parameter informations of the particular stored procedure
Let us consider the following procedure
create procedure testing
(
@id int,
@user_name varchar(100),
@join_date datetime
)
as
select @id as userid, @user_name as username,@join_date ......
|
|
-
|
|
There are many ways to know the version of the Current Server using a query. They are shown below
1 Use @@version
select @@version
2 Use system procedure sp_server_info and see the value of attribute_value column where attribute_name is DBMS_VER
EXEC sp_server_info
3 Use system procedure e......
|
|
-
|
|
Regular expression can be effectively used to get performance benefit in certain cases. Consider the following customers table where custid is the primary key.
Source Data
declare @customers table
(
custid varchar(10) primary key,
custname varchar(100),
zipcode char(6)
)
insert into @custo......
|
|
-
|
|
The system stored procedure sp_tables is used to list out the tables available in the current database of the current server. What if you want to know the same that exist in the linked Server? You can use sp_tables_ex
The following returns list of tables available in the specified Server and datab......
|
|
-
|
|
Suppose you want to find out the name of the procedure that calls the procedure being executed,you can use system function @@PROCID which returns the object id of the current procedure.
Consider the following procedures. test1 calls the procedure test2 which actually logs some informations to the ......
|
|
-
|
|
Here are the three different ways to know the Server name using queries
Method 1 : Use @@servername
select @@servername as server_name
Method 2 : Use sp_helpserver system procedure
exec sp_helpserver
Method 3 : Use sys.sysservers view
select srvname from sys.sysservers
......
|
|
-
|
|
Many programming languages like VB6 support IIF function which is used to return a value based on a particular expression/condition. In SQL Server the equivalent is to make use of CASE Expression, which is used to evaluate many conditions and return a single value. The following examples will give y......
|
|
-
|
|
As you know, @@TRANCOUNT is used to inform you about the number of live transactions active at point. If you want to retrieve it's value using an application, you should be careful about the behaviour of the returned value. Let us consider the procedure
create procedure get_count
as
select @@TRA......
|
|
-
|
|
One of my friends asked me if it is possible to identify the tables with identiy columns as he wanted to run dbcc checkident command to all the tables of the database.There are actually many ways to identify identity columns from a table.The following three methods will list out the table names and ......
|
|