tablas - En SQL Server, ¿cómo puedo generar una sentencia CREATE TABLE para una tabla dada?
insertar datos en una tabla sql (15)
He dedicado una buena cantidad de tiempo a encontrar la solución a este problema, por lo que, en el espíritu de esta publicación , la publico aquí, ya que creo que podría ser útil para otros.
Si alguien tiene una secuencia de comandos mejor o algo para agregar, publíquelo.
Editar: Sí chicos, sé cómo hacerlo en Management Studio, pero necesitaba poder hacerlo desde otra aplicación.
- o podría crear un procedimiento almacenado ... primero con la creación de Id.
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
- y segundo sin iD INSERCIÓN
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
Algo que he notado - en la vista INFORMATION_SCHEMA.COLUMNS, CHARACTER_MAXIMUM_LENGTH da un tamaño de 2147483647 (2 ^ 31-1) para los tipos de campo como imagen y texto. ntext es 2 ^ 30-1 (siendo unicode de doble byte y todo).
Este tamaño se incluye en el resultado de esta consulta, pero no es válido para estos tipos de datos en una declaración CREATE (no deberían tener un valor de tamaño máximo). Entonces, a menos que los resultados de esto se corrijan manualmente, el script CREATE no funcionará dado estos tipos de datos.
Me imagino que es posible arreglar el script para dar cuenta de esto, pero eso está más allá de mis capacidades de SQL.
Aquí está el guión que se me ocurrió. Maneja columnas de identidad, valores predeterminados y claves principales. No maneja claves externas, índices, activadores o cualquier otra cosa inteligente. Funciona en SQLServer 2000, 2005 y 2008.
declare @table varchar(100)
set @table = ''MyTable'' -- set table name here
declare @sql table(s varchar(1000), id int identity)
-- create statement
insert into @sql(s) values (''create table ['' + @table + ''] ('')
-- column list
insert into @sql(s)
select
'' [''+column_name+''] '' +
data_type + coalesce(''(''+cast(character_maximum_length as varchar)+'')'','''') + '' '' +
case when exists (
select id from syscolumns
where object_name(id)=@table
and name=column_name
and columnproperty(id,name,''IsIdentity'') = 1
) then
''IDENTITY('' +
cast(ident_seed(@table) as varchar) + '','' +
cast(ident_incr(@table) as varchar) + '')''
else ''''
end + '' '' +
( case when IS_NULLABLE = ''No'' then ''NOT '' else '''' end ) + ''NULL '' +
coalesce(''DEFAULT ''+COLUMN_DEFAULT,'''') + '',''
from INFORMATION_SCHEMA.COLUMNS where table_name = @table
order by ordinal_position
-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = @table and constraint_type=''PRIMARY KEY''
if ( @pkname is not null ) begin
insert into @sql(s) values('' PRIMARY KEY ('')
insert into @sql(s)
select '' [''+COLUMN_NAME+''],'' from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where constraint_name = @pkname
order by ordinal_position
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
insert into @sql(s) values ('' )'')
end
else begin
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
end
-- closing bracket
insert into @sql(s) values( '')'' )
-- result!
select s from @sql order by id
Crédito debido a @Blorgbeard por compartir su guión. Sin duda lo marcaré en caso de que lo necesite.
Sí, puede hacer clic con el botón derecho en la tabla y ejecutar el script CREATE TABLE , pero:
- El script a contendrá cargas de cruft (¿interesado en las propiedades extendidas?)
- Si tiene más de 200 tablas en su esquema, le tomará medio día programar el lote a mano.
Con esta secuencia de comandos convertida en un procedimiento almacenado, y combinada con una secuencia de comandos contenedora, tendría una forma agradable y automática de volcar su diseño de tabla en control de fuente, etc.
El resto del código de su base de datos (SP, índices FK, desencadenantes, etc.) estaría bajo control de fuente de todos modos;)
Hay un script de Powershell enterrado en los foros de msdb que script todas las tablas y objetos relacionados:
# Script all tables in a database
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
| out-null
$s = new-object (''Microsoft.SqlServer.Management.Smo.Server'') ''<Servername>''
$db = $s.Databases[''<Database>'']
$scrp = new-object (''Microsoft.SqlServer.Management.Smo.Scripter'') ($s)
$scrp.Options.AppendToFile = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $False
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True
$scrp.Options.FileName = ''C:/Temp/<Database>.SQL''
foreach($item in $db.Tables) { $tablearray+=@($item) }
$scrp.Script($tablearray)
Write-Host "Scripting complete"
Incluyo definiciones para columnas calculadas
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'')
Me doy cuenta de que ha pasado mucho tiempo, pero pensé que agregaría de todos modos. Si solo quiere la tabla, y no la declaración create table, puede usar
select into x from db.schema.y where 1=0
para copiar la tabla a una nueva base de datos
Modifiqué la respuesta aceptada y ahora puede obtener el comando incluyendo la clave principal y la clave externa en un esquema determinado.
declare @table varchar(100)
declare @schema varchar(100)
set @table = ''Persons'' -- set table name here
set @schema = ''OT'' -- set SCHEMA name here
declare @sql table(s varchar(1000), id int identity)
-- create statement
insert into @sql(s) values (''create table '' + @table + '' ('')
-- column list
insert into @sql(s)
select
'' ''+column_name+'' '' +
data_type + coalesce(''(''+cast(character_maximum_length as varchar)+'')'','''') + '' '' +
case when exists (
select id from syscolumns
where object_name(id)=@table
and name=column_name
and columnproperty(id,name,''IsIdentity'') = 1
) then
''IDENTITY('' +
cast(ident_seed(@table) as varchar) + '','' +
cast(ident_incr(@table) as varchar) + '')''
else ''''
end + '' '' +
( case when IS_NULLABLE = ''No'' then ''NOT '' else '''' end ) + ''NULL '' +
coalesce(''DEFAULT ''+COLUMN_DEFAULT,'''') + '',''
from information_schema.columns where table_name = @table and table_schema = @schema
order by ordinal_position
-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type=''PRIMARY KEY''
if ( @pkname is not null ) begin
insert into @sql(s) values('' PRIMARY KEY ('')
insert into @sql(s)
select '' ''+COLUMN_NAME+'','' from information_schema.key_column_usage
where constraint_name = @pkname
order by ordinal_position
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
insert into @sql(s) values ('' )'')
end
else begin
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
end
-- foreign key
declare @fkname varchar(100)
select @fkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type=''FOREIGN KEY''
if ( @fkname is not null ) begin
insert into @sql(s) values('','')
insert into @sql(s) values('' FOREIGN KEY ('')
insert into @sql(s)
select '' ''+COLUMN_NAME+'','' from information_schema.key_column_usage
where constraint_name = @fkname
order by ordinal_position
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
insert into @sql(s) values ('' ) REFERENCES '')
insert into @sql(s)
SELECT
OBJECT_NAME(fk.referenced_object_id)
FROM
sys.foreign_keys fk
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
INNER JOIN
sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
where fk.name = @fkname
insert into @sql(s)
SELECT
''(''+c2.name+'')''
FROM
sys.foreign_keys fk
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
INNER JOIN
sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
where fk.name = @fkname
end
-- closing bracket
insert into @sql(s) values( '')'' )
-- result!
select s from @sql order by id
Modifiqué la versión anterior para ejecutarla en todas las tablas y admitir nuevos tipos de datos de SQL 2005. También conserva los nombres de las teclas principales. Funciona solo en SQL 2005 (usando cross apply).
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'')
Actualización: manejo adicional del tipo de datos XML
Actualización 2: casos solucionados cuando 1) hay varias tablas con el mismo nombre pero con esquemas diferentes, 2) hay varias tablas que tienen restricción PK con el mismo nombre
Si la aplicación de la que está generando los scripts es una aplicación .NET, es posible que desee estudiar el uso de SMO (objetos de administración Sql). Consulte este enlace del Equipo SQL sobre cómo usar SMO para guiar objetos.
Una variante más con soporte de claves externas y en una declaración:
SELECT
obj.name
,''CREATE TABLE ['' + 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 object_name(id) = obj.name
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 information_schema.columns.column_default IS NOT NULL THEN '' DEFAULT '' + information_schema.columns.column_default ELSE '''' END
+ '',''
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE table_name = obj.name
ORDER BY ordinal_position
FOR XML PATH('''')
) cols (list)
CROSS APPLY(
SELECT
CHAR(10) + ''ALTER TABLE '' + obj.name + ''_noident_temp 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://.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema
SELECT
'' REFERENCES ['' + kcu1.constraint_schema + ''].'' + ''['' + kcu2.table_name + '']'' + ''('' + kcu2.column_name + ''), ''
FROM information_schema.referential_constraints as rc
JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name)
JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position)
WHERE
kcu1.constraint_catalog = tc.constraint_catalog AND kcu1.constraint_schema = tc.constraint_schema AND kcu1.constraint_name = tc.constraint_name
) r (list)
WHERE tc.table_name = obj.name
FOR XML PATH('''')
) a (alt)
) refs (list)
WHERE
xtype = ''U''
AND name NOT IN (''dtproperties'')
AND obj.name = ''your_table_name''
Puedes intentarlo en sqlfiddle: http://sqlfiddle.com/#!6/e3b66/3/0
Voy a mejorar la respuesta al admitir tablas particionadas:
encuentre el esquema de partición y la clave de partición usando scritps a continuación:
declare @partition_scheme varchar(100) = (
select distinct ps.Name AS PartitionScheme
from sys.indexes i
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
where i.object_id = object_id(''your table name'')
)
print @partition_scheme
declare @partition_column varchar(100) = (
select c.name
from sys.tables t
join sys.indexes i
on(i.object_id = t.object_id
and i.index_id < 2)
join sys.index_columns ic
on(ic.partition_ordinal > 0
and ic.index_id = i.index_id and ic.object_id = t.object_id)
join sys.columns c
on(c.object_id = ic.object_id
and c.column_id = ic.column_id)
where t.object_id = object_id(''your table name'')
)
print @partition_column
luego cambie la consulta de generación agregando la línea siguiente en el lugar correcto:
+ IIF(@partition_scheme is null, '''', ''ON ['' + @partition_scheme + ''](['' + @partition_column + ''])'')
Mostrar tabla de creación en asp clásico (maneja restricciones, claves principales, copiando la estructura de tabla y / o datos ...)
Servidor Sql Muestra los comandos "Show create table" y "show create database" de Mysql-style del servidor sql de Microsoft. La secuencia de comandos está escrita en Microsoft asp-language y es bastante fácil de trasladar a otro idioma. *
Soporte para esquemas:
Esta es una versión actualizada que enmienda la gran respuesta de David, et al. Se agregó soporte para esquemas con nombre. Cabe señalar que esto puede romperse si en realidad hay tablas del mismo nombre presentes en varios esquemas. Otra mejora es el uso de la función oficial QuoteName() .
SELECT
t.TABLE_CATALOG,
t.TABLE_SCHEMA,
t.TABLE_NAME,
''create table ''+QuoteName(t.TABLE_SCHEMA)+''.'' + QuoteName(so.name) + '' ('' + LEFT(o.List, Len(o.List)-1) + ''); ''
+ CASE WHEN tc.Constraint_Name IS NULL THEN ''''
ELSE
''ALTER TABLE '' + QuoteName(t.TABLE_SCHEMA)+''.'' + QuoteName(so.name)
+ '' ADD CONSTRAINT '' + tc.Constraint_Name + '' PRIMARY KEY '' + '' ('' + LEFT(j.List, Len(j.List)-1) + ''); ''
END as ''SQL_CREATE_TABLE''
FROM sysobjects so
CROSS APPLY (
SELECT
'' [''+column_name+''] ''
+ data_type
+ case data_type
when ''sql_variant'' then ''''
when ''text'' then ''''
when ''ntext'' 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
+ '','' -- can''t have a field name or we''ll end up with XML
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''
LEFT JOIN information_schema.tables t on
t.Table_name = so.Name
CROSS APPLY (
SELECT QuoteName(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'')
-- AND so.name = ''ASPStateTempSessions''
;
..
Para uso en Management Studio:
Un detractor del código sql anterior es que si lo prueba usando SSMS, las declaraciones largas no son fáciles de leer. Entonces, según esta útil publicación , aquí hay otra versión que está algo modificada para que sea más fácil para los ojos luego de hacer clic en el enlace de una celda en la grilla. Los resultados son más fácilmente identificables como sentencias CREATE TABLE muy bien formateadas para cada tabla en el db.
-- settings
DECLARE @CRLF NCHAR(2)
SET @CRLF = Nchar(13) + NChar(10)
DECLARE @PLACEHOLDER NCHAR(3)
SET @PLACEHOLDER = ''{:}''
-- the main query
SELECT
t.TABLE_CATALOG,
t.TABLE_SCHEMA,
t.TABLE_NAME,
CAST(
REPLACE(
''create table '' + QuoteName(t.TABLE_SCHEMA) + ''.'' + QuoteName(so.name) + '' ('' + @CRLF
+ LEFT(o.List, Len(o.List) - (LEN(@PLACEHOLDER)+2)) + @CRLF + '');'' + @CRLF
+ CASE WHEN tc.Constraint_Name IS NULL THEN ''''
ELSE
''ALTER TABLE '' + QuoteName(t.TABLE_SCHEMA) + ''.'' + QuoteName(so.Name)
+ '' ADD CONSTRAINT '' + tc.Constraint_Name + '' PRIMARY KEY ('' + LEFT(j.List, Len(j.List) - 1) + '');'' + @CRLF
END,
@PLACEHOLDER,
@CRLF
)
AS XML) as ''SQL_CREATE_TABLE''
FROM sysobjects so
CROSS APPLY (
SELECT
'' ''
+ ''[''+column_name+''] ''
+ data_type
+ case data_type
when ''sql_variant'' then ''''
when ''text'' then ''''
when ''ntext'' 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
+ '', ''
+ @PLACEHOLDER -- note, can''t have a field name or we''ll end up with XML
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''
LEFT JOIN information_schema.tables t on
t.Table_name = so.Name
CROSS APPLY (
SELECT QUOTENAME(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'')
-- AND so.name = ''ASPStateTempSessions''
;
No es necesario aclarar el punto, pero aquí están los resultados del ejemplo funcionalmente equivalentes para la comparación:
-- 1 (scripting version)
create table [dbo].[ASPStateTempApplications] ( [AppId] int NOT NULL , [AppName] char(280) NOT NULL ); ALTER TABLE [dbo].[ASPStateTempApplications] ADD CONSTRAINT PK__ASPState__8E2CF7F908EA5793 PRIMARY KEY ([AppId]);
-- 2 (SSMS version)
create table [dbo].[ASPStateTempSessions] (
[SessionId] nvarchar(88) NOT NULL ,
[Created] datetime NOT NULL DEFAULT (getutcdate()),
[Expires] datetime NOT NULL ,
[LockDate] datetime NOT NULL ,
[LockDateLocal] datetime NOT NULL ,
[LockCookie] int NOT NULL ,
[Timeout] int NOT NULL ,
[Locked] bit NOT NULL ,
[SessionItemShort] varbinary(7000) NULL ,
[SessionItemLong] image(2147483647) NULL ,
[Flags] int NOT NULL DEFAULT ((0))
);
ALTER TABLE [dbo].[ASPStateTempSessions] ADD CONSTRAINT PK__ASPState__C9F4929003317E3D PRIMARY KEY ([SessionId]);
..
Factores que distraen:
Cabe señalar que sigo estando relativamente insatisfecho con esto debido a la falta de soporte para indeces que no sean una clave principal. Sigue siendo adecuado para su uso como mecanismo de exportación o replicación simple de datos.
Si está utilizando Management Studio y tiene abierta la ventana del analizador de consultas, puede arrastrar el nombre de la tabla a la ventana del analizador de consultas y ... ¡bingo! obtienes la secuencia de comandos de la tabla. No he intentado esto en SQL2008