Create SQL script for Tables in Database

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')
Advertisements

Select one row from detail records based on condition.

I often come across the issue to return one row from detail table like invoice items or order details for one customer for specific condition.
i.e. show me the max order product for each customer and order

that kind of query could be solved using self join, grouping and having clause

we have three tables, 1. customer 2. Order 3. OrderItem

the query look like

select c.customer_id, oi.order_id, oi.product_cost from order_item oi 
inner join order_item oii on oi.order_id = oii.order_id
inner join customer c on c.customer_id = oi.customer_id
group by c.customer_id, oi.order_id, oi.product_cost
having oi.product_cost >= max(oii.product_cost)

SQL SERVER TRY..CATCH

In SQL Server 2000, Exception was handle by the @@error variable where after each statement SQL Server sets @@error to “0” if the statement was successful, and if not then it will filled by the error number.

you can also use RAISEERROR() function to raise custom erorr when you needed. There was no mechanism such as Exception TRY.. CATCH block in SQL 2000. After each statement you have to check the @@error Variable and then raise an error.

From SQL 2005, errors can be handled in T-SQL same way as C# or any .net programming language by TRY..CATCH block.

syntex:

BEING TRY
{sql_statement| statement block}
END TRY
BEGIN CATCH
{sql_statement| statement block}
END CATCH

A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.

In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:

* ERROR_NUMBER() returns the number of the error.
* ERROR_SEVERITY() returns the severity.
* ERROR_STATE() returns the error state number.
* ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
* ERROR_LINE() returns the line number inside the routine that caused the error.
* ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

for more information..
Use TRY.. CATCH in transaction

BEGIN TRANSACTION
BEGIN TRY
--
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNR > 0
ROLLBACK TRANSACTION
END CATCH;
IF @@TRANSCOUNT > 0
COMMIT TRANSACTION;
TO

For More information..(More)