refanurse.blogg.se

INSERT INTO INTO CHAR(10) + FieldName + ' ' + INSERT INTO TABLE ' + CASE WHEN IS NOT NULL THEN + '.' + + '.' + ELSE + '.' + + '.' + END) SELECT TOP 1 = TableOwner FROM INTO (Flds) VALUES('(') WHERE c.TABLE_NAME = BY c.TABLE_NAME, c.ORDINAL_POSITION LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D' JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name ,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn ,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS Identit圜olumn ,CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END WHERE inf.TABLE_NAME = and BY inf.ORDINAL_POSITION Join INFORMATION_SCHEMA.COLUMNS inf on t.name=inf.TABLE_NAME

Left join sys.computed_columns cc on t.object_id=cc.object_id and c.column_id=cc.column_id LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = c.default_object_id AND dobj.type = ''D'' Left JOIN sys.types st ON st.system_type_id=c.system_type_id and st.principal_id=t.object_id-COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name LEFT JOIN sys.identity_columns ic ON t.object_id=ic.object_id AND c.column_id=ic.column_id JOIN sys.columns c ON t.object_id=c.object_id -AND s.schema_id=c.schema_id Join sys.schemas s on t.schema_id=s.schema_id ,CASE WHEN c.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumnįrom (select schema_id,object_id,name from sys.views union all select schema_id,object_id,name from sys.tables)t ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed ,CASE WHEN ic.object_id IS not NULL and ic.seed_value is not null THEN 1 ELSE 0 END AS Identit圜olumn-CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS Identit圜olumn caso di viste, dà come campo identity ma nn dà i valori, quindi lo ignoro ,case inf.CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(inf.CHARACTER_MAXIMUM_LENGTH AS varchar) end-CAST(CHARACTER_MAXIMUM_LENGTH AS INT) ,CASE WHEN inf.IS_NULLABLE = ''YES'' THEN 1 ELSE 0 END SELECT = name FROM sysobjects WHERE id = TABLE (FieldID INT IDENTITY(1,1)ĭECLARE TABLE(FldID SMALLINT IDENTITY(1,1)ĭECLARE TABLE(DefinitionID SMALLINT IDENTITY(1,1)

If not exists (select * from sys.types where name = 'TableType')Ĭreate type TableType as table (ObjectID int)-drop type TableType Here the SP: create PROCEDURE BIT = BIT = SYSNAME = BIT = varchar(max) output

I wrote this sp to create the schema automatically with all things, pk, fk, partitions, constraints.īefore exec create type TableType as table (ObjectID int)