Dans SQL Server, comment générer une instruction CREATE TABLE pour une table donnée ?

Question

Dans SQL Server, comment générer une instruction CREATE TABLE pour une table donnée ?

Demandé el 22 de Août, 2008: Quand la question a-t-elle été
53390 affichage: Nombre de visites la question a
5 Réponses: Nombre de réponses aux questions
Résolu: Situation réelle de la question

J'ai passé une bonne partie du temps à trouver une solution à ce problème, donc dans l'esprit du ce poste Je le publie ici, car je pense qu'il peut être utile à d'autres.

Si quelqu'un a un meilleur script, ou quelque chose à ajouter, merci de le poster.

Edit : Oui les gars, je sais comment le faire dans Management Studio - mais j'avais besoin de pouvoir le faire à partir d'une autre application.

Demandé el 22 de Août, 2008 par Blorgbeard

Answer 1

5 Réponses

Answer 2

4voto

YordanGeorgiev Points 1222

-- ou vous pouvez créer une procédure stockée ... d'abord avec la création de l'identité

USE [db]
GO

/****** Object:  StoredProcedure [dbo].[procUtils_InsertGeneratorWithId]    Script Date: 06/13/2009 22:18:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROC [dbo].[procUtils_InsertGeneratorWithId]    
(    
@domain_user varchar(50),    
@tableName varchar(100)    
)     

as    

--Declare a cursor to retrieve column specific information for the specified table    
DECLARE cursCol CURSOR FAST_FORWARD FOR     
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName    
OPEN cursCol    
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement    
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement    
DECLARE @dataType nvarchar(1000) --data types returned for respective columns    
DECLARE @IDENTITY_STRING nvarchar ( 100 )    
SET @IDENTITY_STRING = ' '     
select  @IDENTITY_STRING    
SET @string='INSERT '+@tableName+'('    
SET @stringData=''    

DECLARE @colName nvarchar(50)    

FETCH NEXT FROM cursCol INTO @colName,@dataType    

IF @@fetch_status<>0    
 begin    
 print 'Table '+@tableName+' not found, processing skipped.'    
 close curscol    
 deallocate curscol    
 return    
END    

WHILE @@FETCH_STATUS=0    
BEGIN    
IF @dataType in ('varchar','char','nchar','nvarchar')    
BEGIN    
 --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'    
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'    
END    
ELSE    
if @dataType in ('text','ntext') --if the datatype is text or something else     
BEGIN    
 SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'    
END    
ELSE    
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly    
BEGIN    
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'    
END    
ELSE     
IF @dataType='datetime'    
BEGIN    
 --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'    
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
 --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'    
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'    
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
END    
ELSE     
IF @dataType='image'     
BEGIN    
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'    
END    
ELSE --presuming the data type is int,bit,numeric,decimal     
BEGIN    
 --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'    
 --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'    
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'    
END    

SET @string=@string+@colName+','    

FETCH NEXT FROM cursCol INTO @colName,@dataType    
END    
DECLARE @Query nvarchar(4000)    

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName    
exec sp_executesql @query    
--select @query    

CLOSE cursCol    
DEALLOCATE cursCol    

  /*
USAGE

*/

GO

-- et deuxième sans INSERTION iD

USE [db]
GO

/****** Object:  StoredProcedure [dbo].[procUtils_InsertGenerator]    Script Date: 06/13/2009 22:20:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[procUtils_InsertGenerator]        
(        
@domain_user varchar(50),        
@tableName varchar(100)        
)         

as        

--Declare a cursor to retrieve column specific information for the specified table        
DECLARE cursCol CURSOR FAST_FORWARD FOR         

-- SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName        
/* NEW     
SELECT c.name , sc.data_type  FROM sys.extended_properties AS ep                   
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id                   
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id                   
= c.column_id                   
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and                   
c.name = sc.column_name                   
WHERE t.name = @tableName and c.is_identity=0      
  */      

select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"      
  from sys.columns c          
  join sys.systypes s on (s.xtype = c.system_type_id)          
  where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')          
   AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName  ) and c.is_identity=0 and s.name not like 'sysname'  

OPEN cursCol        
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement        
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement        
DECLARE @dataType nvarchar(1000) --data types returned for respective columns        
DECLARE @IDENTITY_STRING nvarchar ( 100 )        
SET @IDENTITY_STRING = ' '         
select  @IDENTITY_STRING        
SET @string='INSERT '+@tableName+'('        
SET @stringData=''        

DECLARE @colName nvarchar(50)        

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        

IF @@fetch_status<>0        
 begin        
 print 'Table '+@tableName+' not found, processing skipped.'        
 close curscol        
 deallocate curscol        
 return        
END        

WHILE @@FETCH_STATUS=0        
BEGIN        
IF @dataType in ('varchar','char','nchar','nvarchar')        
BEGIN        
 --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'        
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'        
END        
ELSE        
if @dataType in ('text','ntext') --if the datatype is text or something else         
BEGIN        
 SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'        
END        
ELSE        
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly        
BEGIN        
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'        
END        
ELSE         
IF @dataType='datetime'        
BEGIN        
 --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'        
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations        
 --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'        
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'        
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations        
END        
ELSE         
IF @dataType='image'         
BEGIN        
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'        
END        
ELSE --presuming the data type is int,bit,numeric,decimal         
BEGIN        
 --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'        
 --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'        
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'        
END        

SET @string=@string+@colName+','        

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        
END        
DECLARE @Query nvarchar(4000)        

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName        
exec sp_executesql @query        
--select @query       

CLOSE cursCol        
DEALLOCATE cursCol        

  /*      

use poc     
go    

DECLARE @RC int      
DECLARE @domain_user varchar(50)      
DECLARE @tableName varchar(100)      

-- TODO: Set parameter values here.      
set @domain_user='yorgeorg'      
set @tableName = 'tbGui_WizardTabButtonAreas'      

EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]       
   @domain_user      
  ,@tableName      

*/
GO

Répondu el 13 de Juin, 2009 par YordanGeorgiev (1222 Points )

Answer 3

3voto

viljun Points 304

Afficher la création d'un tableau en asp classique (gérer les contraintes, les clés primaires, copier la structure de la table et/ou les données...)

Sql server Show create table Commandes "Show create table" et "show create database" de style Mysql de Microsoft sql server. Le script est écrit en langage Microsoft asp et est assez facile à porter vers un autre langage.*

Répondu el 14 de Janvier, 2011 par viljun (304 Points )

Answer 4

2voto

Erick Langford Xenes Points 834

J'inclus les définitions des colonnes calculées

    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, name
from    sysobjects so
cross apply
    (SELECT

case when comps.definition is not null then '  ['+column_name+'] AS ' + comps.definition 
else
        '  ['+column_name+'] ' + data_type + 
        case
        when data_type like '%text' or data_type in ('image', 'sql_variant' ,'xml')
            then ''
        when data_type in ('float')
            then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ')'
        when data_type in ('datetime2', 'datetimeoffset', 'time')
            then '(' + cast(coalesce(datetime_precision, 7) as varchar(11)) + ')'
        when data_type in ('decimal', 'numeric')
            then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ',' + cast(coalesce(numeric_scale, 0) as varchar(11)) + ')'
        when (data_type like '%binary' or data_type like '%char') and character_maximum_length = -1
            then '(max)'
        when character_maximum_length is not null
            then '(' + cast(character_maximum_length as varchar(11)) + ')'
        else ''
        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 information_schema.columns.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 
end + ', ' 

     from information_schema.columns 
     left join sys.computed_columns comps 
     on OBJECT_ID(information_schema.columns.TABLE_NAME)=comps.object_id and information_schema.columns.COLUMN_NAME=comps.name

     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')

Répondu el 24 de Janvier, 2017 par Erick Langford Xenes (834 Points )

Answer 5

2voto

Stu Points 504

Je me rends compte que cela fait très longtemps, mais j'ai pensé que je devais quand même ajouter quelque chose. Si vous voulez juste la table, et pas l'instruction create table, vous pouvez utiliser

select into x from db.schema.y where 1=0

pour copier la table vers une nouvelle BD

Répondu el 6 de Octobre, 2017 par Stu (504 Points )

Answer 6

1voto

AMieres Points 88

Une requête basée sur la réponse de Hubbitus.

inclut les noms de schémas
corrige les clés étrangères avec plus d'un champ
inclut CASCADE UPDATE & DELETE
comprend un DROP TABLE conditionné

SELECT Schema_Name = SCHEMA_NAME(obj.uid) , Table_Name = name , Drop_Table = 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''' + SCHEMA_NAME(obj.uid) + ''' AND TABLE_NAME = ''' + obj.name + ''')) DROP TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] ' , Create_Table =' CREATE TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] (' + LEFT(cols.list, LEN(cols.list) - 1 ) + ')' + ISNULL(' ' + refs.list, '') FROM sysobjects obj CROSS APPLY ( SELECT CHAR(10)
- ' [' + 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 ( -- Identity skip select id from syscolumns where id = obj.id and name = column_name and columnproperty(id, name, 'IsIdentity') = 1 ) then 'IDENTITY(' + cast(ident_seed(obj.name) as varchar) + ',' + cast(ident_incr(obj.name) as varchar) + ')' else '' end + ' '
- CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END
- 'NULL'
- CASE WHEN IC.column_default IS NOT NULL THEN ' DEFAULT ' + IC.column_default ELSE '' END
- ',' FROM INFORMATION_SCHEMA.COLUMNS IC WHERE IC.table_name = obj.name AND IC.TABLE_SCHEMA = SCHEMA_NAME(obj.uid) ORDER BY ordinal_position FOR XML PATH('') ) cols (list) CROSS APPLY( SELECT CHAR(10) + 'ALTER TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] ADD ' + LEFT(alt, LEN(alt)-1) FROM( SELECT CHAR(10)
  - ' CONSTRAINT ' + tc.constraint_name
  - ' ' + tc.constraint_type + ' (' + LEFT(c.list, LEN(c.list)-1) + ')'
  - COALESCE(CHAR(10) + r.list, ', ') FROM information_schema.table_constraints tc CROSS APPLY( SELECT '[' + kcu.column_name + '], ' FROM information_schema.key_column_usage kcu WHERE kcu.constraint_name = tc.constraint_name ORDER BY kcu.ordinal_position FOR XML PATH('') ) c (list) OUTER APPLY( -- // http://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema SELECT LEFT(f.list, LEN(f.list)-1) + ')' + IIF(rc.DELETE_RULE = 'NO ACTION', '', ' ON DELETE ' + rc.DELETE_RULE) + IIF(rc.UPDATE_RULE = 'NO ACTION', '', ' ON UPDATE ' + rc.UPDATE_RULE) + ', ' FROM information_schema.referential_constraints rc CROSS APPLY( SELECT IIF(kcu.ordinal_position = 1, ' REFERENCES [' + kcu.table_schema + '].[' + kcu.table_name + '] (', '')
    - '[' + kcu.column_name + '], ' FROM information_schema.key_column_usage kcu WHERE kcu.constraint_catalog = rc.unique_constraint_catalog AND kcu.constraint_schema = rc.unique_constraint_schema AND kcu.constraint_name = rc.unique_constraint_name ORDER BY kcu.ordinal_position FOR XML PATH('') ) f (list) WHERE rc.constraint_catalog = tc.constraint_catalog AND rc.constraint_schema = tc.constraint_schema AND rc.constraint_name = tc.constraint_name ) r (list) WHERE tc.table_name = obj.name FOR XML PATH('') ) a (alt) ) refs (list) WHERE xtype = 'U'

Pour combiner la suppression d'une table (si elle existe) avec la création d'une table, utilisez cette méthode :

SELECT Drop_Table + CHAR(10) + Create_Table FROM SysCreateTables

Répondu el 13 de Septembre, 2020 par AMieres (88 Points )

Dans SQL Server, comment générer une instruction CREATE TABLE pour une table donnée ?

Réponses

Questions en vedette

Top Tags

Prograide.com

Powered by:

Dans SQL Server, comment générer une instruction CREATE TABLE pour une table donnée ?

Réponses

Questions en vedette

Top Tags

Dans notre réseau

Prograide.com

Powered by: