Sunday 20 March 2016

How to generate create table script for all tables in SQL Server database?

(1) Get all tables scripts.

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 'decimal' then '(' + cast(numeric_precision_radix 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')

(2) Get all Stored Procedure.

SELECT STUFF(S.script, CHARINDEX(P.name, S.script, 1), 0, N'sp_helptext')
  FROM sys.procedures AS P
       CROSS APPLY

       (SELECT OBJECT_DEFINITION(P.object_id) AS script) AS S

(3) Get all views.

SELECT STUFF(S.script, CHARINDEX(P.name, S.script, 1), 0, N'sp_helptext')
  FROM sys.views AS P
       CROSS APPLY
       (SELECT OBJECT_DEFINITION(P.object_id) AS script) AS S

(4) Get All UserDefined Function.

SELECT name AS function_name,SCHEMA_NAME(schema_idAS schema_name,type_descFROM sys.objectsWHERE type_desc LIKE '%FUNCTION%';
     
     
       

1 comment:

  1. Thanks for sharing this blog about how to generate create table script in dot net. If you want to Hire Dot Net Developers for this specific task then you must discuss these important things.

    ReplyDelete