sql : cursor , create cursor

declare @_CName varchar(100)
declare _cursor cursor read_only for
select Name from dbSample.dbo.Customers
open _cursor
fetch next from _cursor into @_CName
while @@FETCH_STATUS=0
begin
print @_CName
fetch next from _cursor into  @_CName
end

close _cursor
deallocate _cursor


In Capital Letter

DECLARE @_CNAME VARCHAR(100)
DECLARE _CURSOR CURSOR READ_ONLY FOR
SELECT NAME FROM dbSample.dbo.CUSTOMERS
OPEN _CURSOR
FETCH NEXT FROM _CURSOR INTO @_CNAME
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @_CNAME
FETCH NEXT FROM _CURSOR INTO  @_CNAME
END

CLOSE _CURSOR

DEALLOCATE _CURSOR

sql : get all the table in a database

select *  from information_schema.tables
where TABLE_CATALOG = 'databaseName' and  table_schema = 'schemaname' and  TABLE_TYPE = 'BASE TABLE' and table_name LIKE 'App_CandidateLoginDetail'

or SELECT [name] AS 'TableName' FROM SYSOBJECTS WHERE xtype = 'U' order by [name];

example

select *  from information_schema.tables
where TABLE_CATALOG = 'dbSample' and  table_schema = 'dbo' and  TABLE_TYPE = 'BASE TABLE' and table_name LIKE 'App_CandidateLoginDetail'

SELECT * INTO

I have a table and I want to create the same structure and copy all the data from this table .
All the structure are same but the table name is different .
Am using this query.
As a result I have 2 tables having same table structure and same data with different table name .
Here My existing table is tblOld   and my new table is tblNew  .

SELECT * INTO tblNew  FROM tblOld ;

After run this query I got same structure of tblNew   as of tblOld .

sql : Know relationships between all the tables of database in SQL Server

SELECT
    fk.name      as 'Foreign Key Name',
    tp.name      as 'Foreign Key Table Name',
    cp.column_id as 'Foreign Key Column ID',      
    cp.name      as 'Foreign Key Column Name' ,  
    tr.name      as 'Primary Key Table Name',
    cr.column_id as 'Primary Key Column ID',
    cr.name      as 'Primary Key Column Name'
FROM
    sys.foreign_keys fk
INNER JOIN
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id

SQL : use of SET XACT_ABORT (Transact-SQL) in store procedure

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

declaration:

SET XACT_ABORT ON;

SQL : select all table name in a database

select * from INFORMATION_SCHEMA.TABLES

SQL : common table expression(cte)

;with cte_demo
as
(
select * from emp where EmpSal>=10000
) select * from cte_demo

sql : select all database in Sql server

I try this and  I get the all database in sql server

select * from master.dbo.Sysdatabases