sql order - Der Grund für die Spalte ist in der Auswahlliste ungültig, da sie weder in einer Aggregatfunktion noch in der GROUP BY-Klausel enthalten ist.





having multiple (5)


Ihre Abfrage wird in MYSQL wenn Sie festlegen, ONLY_FULL_GROUP_BY Server-Modus zu deaktivieren ( und standardmäßig ist es ). In diesem Fall verwenden Sie jedoch unterschiedliche RDBMS. Fügen Sie also alle nicht aggregierten Spalten Ihrer GROUP BY Klausel hinzu, um Ihre Abfrage funktionstüchtig zu machen

SELECT col1, col2, SUM(col3) totalSUM
FROM tableName
GROUP BY col1, col2

Nicht aggregierte Spalten bedeuten, dass die Spalte nicht in aggregierte Funktionen wie SUM , MAX , COUNT usw. COUNT .

Mögliche Duplikate:
GROUP BY / Aggregat Funktion Verwirrung in SQL

Ich habe einen Fehler -

Die Spalte 'Employee.EmpID' ist in der Auswahlliste ungültig, da sie weder in einer Aggregatfunktion noch in der GROUP BY-Klausel enthalten ist.

select loc.LocationID, emp.EmpID
from Employee as emp full join Location as loc 
on emp.LocationID = loc.LocationID
group by loc.LocationID 

Diese Situation passt in die Antwort von Bill Karwin.

Korrektur für oben, passt in die Antwort von ExactaBox -

select loc.LocationID, count(emp.EmpID) -- not count(*), don't want to count nulls
from Employee as emp full join Location as loc 
on emp.LocationID = loc.LocationID
group by loc.LocationID 

ORIGINAL FRAGE -

Für die SQL-Abfrage -

select *
from Employee as emp full join Location as loc 
on emp.LocationID = loc.LocationID
group by (loc.LocationID)

Ich verstehe nicht, warum ich diesen Fehler bekomme. Ich möchte nur an den Tischen teilnehmen und alle Mitarbeiter an einem bestimmten Ort zusammen gruppieren.

Ich denke, ich habe eine teilweise Erklärung für meine eigene Frage. Sag mir, wenn es in Ordnung ist -

Um alle Mitarbeiter, die am gleichen Ort arbeiten, zu gruppieren, müssen wir zuerst die LocationID erwähnen.

Dann können wir nicht jede Mitarbeiter-ID daneben erwähnen. Vielmehr erwähnen wir die Gesamtzahl der Mitarbeiter an diesem Standort, dh wir sollten SUM () die Mitarbeiter an diesem Ort arbeiten. Warum machen wir es so, ich bin mir nicht sicher. Das erklärt also, dass "es ist nicht in einer Aggregatfunktion enthalten" Teil des Fehlers.

Was ist die Erklärung für die GROUP BY Klausel des Fehlers?




"Ich möchte nur die Tische betreten und dann alle Mitarbeiter an einem bestimmten Ort zusammen gruppieren."

Es klingt wie, was Sie wollen, ist für die Ausgabe der SQL-Anweisung, um jeden Mitarbeiter in der Firma aufzulisten, aber zuerst alle Leute in der Anaheim-Büro, dann die Leute in der Buffalo-Büro, dann die Leute in Cleveland Büro (A, B, C, verstehe es, ich weiß natürlich nicht, welche Orte du hast).

In diesem Fall verlieren Sie die GROUP BY-Anweisung. Alles, was Sie brauchen, ist ORDER BY loc.LocationID




Was dieser Fehler eigentlich sagt, ist, dass wenn Sie die GROUP BY Klausel verwenden, Ihr Ergebnis eine Relation / Tabelle mit einer Zeile für jede Gruppe sein wird. In Ihrer SELECT Anweisung können Sie also nur "auswählen" Spalte, nach der Sie gruppieren, und verwenden Aggregatfunktionen für diese Spalte, da die anderen Spalten nicht in der resultierenden Tabelle angezeigt werden.




Angenommen, ich habe die folgende Tabelle T :

a   b
--------
1   abc
1   def
1   ghi
2   jkl
2   mno
2   pqr

Und ich mache die folgende Abfrage:

SELECT a, b
FROM T
GROUP BY a

Die Ausgabe sollte zwei Zeilen haben, eine Zeile mit a=1 und eine zweite Zeile mit a=2 .

Aber was sollte der Wert von b auf jeder dieser beiden Reihen zeigen? Es gibt jeweils drei Möglichkeiten, und nichts in der Abfrage macht deutlich, welcher Wert für b in jeder Gruppe zu wählen ist. Es ist mehrdeutig.

Dies demonstriert die Einzelwertregel , die die undefinierten Ergebnisse verhindert, die Sie beim Ausführen einer GROUP BY-Abfrage erhalten, und alle Spalten in der Auswahlliste, die weder Teil der Gruppierungskriterien sind noch in Aggregatfunktionen (SUM, MIN, MAX usw.).

Es könnte so aussehen:

SELECT a, MAX(b) AS x
FROM T
GROUP BY a

Jetzt ist es klar, dass Sie das folgende Ergebnis wollen:

a   x
--------
1   ghi
2   pqr



Ich habe dieses SP geschrieben, um automatisch das Schema mit allen Dingen, pk, fk, Partitionen, Einschränkungen zu erstellen ...

WICHTIG!! vor der Exekutive

    create type TestTableType as table (ObjectID int)

hier der SP:

create PROCEDURE [dbo].[util_ScriptTable] 
     @DBName SYSNAME
    ,@schema sysname
    ,@TableName SYSNAME
    ,@IncludeConstraints BIT = 1
    ,@IncludeIndexes BIT = 1
    ,@NewTableSchema sysname
    ,@NewTableName SYSNAME = NULL
    ,@UseSystemDataTypes BIT = 0
    ,@script varchar(max) output
AS 
BEGIN try
    if not exists (select * from sys.types where name = 'TableType')
        create type TableType as table (ObjectID int)--drop type TableType

    declare @sql nvarchar(max)

    DECLARE @MainDefinition TABLE (FieldValue VARCHAR(200))
    --DECLARE @DBName SYSNAME
    DECLARE @ClusteredPK BIT
    DECLARE @TableSchema NVARCHAR(255)

    --SET @DBName = DB_NAME(DB_ID())
    SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)

    DECLARE @ShowFields TABLE (FieldID INT IDENTITY(1,1)
                                        ,DatabaseName VARCHAR(100)
                                        ,TableOwner VARCHAR(100)
                                        ,TableName VARCHAR(100)
                                        ,FieldName VARCHAR(100)
                                        ,ColumnPosition INT
                                        ,ColumnDefaultValue VARCHAR(100)
                                        ,ColumnDefaultName VARCHAR(100)
                                        ,IsNullable BIT
                                        ,DataType VARCHAR(100)
                                        ,MaxLength varchar(10)
                                        ,NumericPrecision INT
                                        ,NumericScale INT
                                        ,DomainName VARCHAR(100)
                                        ,FieldListingName VARCHAR(110)
                                        ,FieldDefinition CHAR(1)
                                        ,IdentityColumn BIT
                                        ,IdentitySeed INT
                                        ,IdentityIncrement INT
                                        ,IsCharColumn BIT 
                                        ,IsComputed varchar(255))

    DECLARE @HoldingArea TABLE(FldID SMALLINT IDENTITY(1,1)
                                        ,Flds VARCHAR(4000)
                                        ,FldValue CHAR(1) DEFAULT(0))

    DECLARE @PKObjectID TABLE(ObjectID INT)

    DECLARE @Uniques TABLE(ObjectID INT)

    DECLARE @HoldingAreaValues TABLE(FldID SMALLINT IDENTITY(1,1)
                                                ,Flds VARCHAR(4000)
                                                ,FldValue CHAR(1) DEFAULT(0))

    DECLARE @Definition TABLE(DefinitionID SMALLINT IDENTITY(1,1)
                                        ,FieldValue VARCHAR(200))


  set @sql=
  '
  use '[email protected]+'
  SELECT distinct DB_NAME()
            ,inf.TABLE_SCHEMA
            ,inf.TABLE_NAME
            ,''[''+inf.COLUMN_NAME+'']'' as COLUMN_NAME
            ,CAST(inf.ORDINAL_POSITION AS INT)
            ,inf.COLUMN_DEFAULT
            ,dobj.name AS ColumnDefaultName
            ,CASE WHEN inf.IS_NULLABLE = ''YES'' THEN 1 ELSE 0 END
            ,inf.DATA_TYPE
            ,case inf.CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(inf.CHARACTER_MAXIMUM_LENGTH AS varchar) end--CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
            ,CAST(inf.NUMERIC_PRECISION AS INT)
            ,CAST(inf.NUMERIC_SCALE AS INT)
            ,inf.DOMAIN_NAME
            ,inf.COLUMN_NAME + '',''
            ,'''' AS FieldDefinition
            --caso di viste, dà come campo identity ma nn dà i valori, quindi lo ignoro
            ,CASE WHEN ic.object_id IS not NULL and ic.seed_value is not null THEN 1 ELSE 0 END AS IdentityColumn--CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
            ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
            ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
            ,CASE WHEN c.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn 
            ,cc.definition 
            from (select schema_id,object_id,name from sys.views union all select schema_id,object_id,name from sys.tables)t
                --sys.tables t
            join sys.schemas s on t.schema_id=s.schema_id
            JOIN sys.columns c ON  t.object_id=c.object_id --AND s.schema_id=c.schema_id
            LEFT JOIN sys.identity_columns ic ON t.object_id=ic.object_id AND c.column_id=ic.column_id
            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 OUTER JOIN sys.objects dobj ON dobj.object_id = c.default_object_id AND dobj.type = ''D''
            left join sys.computed_columns cc on t.object_id=cc.object_id and c.column_id=cc.column_id
            join INFORMATION_SCHEMA.COLUMNS inf on t.name=inf.TABLE_NAME
                                               and s.name=inf.TABLE_SCHEMA
                                               and c.name=inf.COLUMN_NAME
    WHERE inf.TABLE_NAME = @TableName and [email protected] 
    ORDER BY inf.ORDINAL_POSITION
    '

  print @sql
  INSERT INTO @ShowFields( DatabaseName
                                    ,TableOwner
                                    ,TableName
                                    ,FieldName
                                    ,ColumnPosition
                                    ,ColumnDefaultValue
                                    ,ColumnDefaultName
                                    ,IsNullable
                                    ,DataType
                                    ,MaxLength
                                    ,NumericPrecision
                                    ,NumericScale
                                    ,DomainName
                                    ,FieldListingName
                                    ,FieldDefinition
                                    ,IdentityColumn
                                    ,IdentitySeed
                                    ,IdentityIncrement
                                    ,IsCharColumn
                                    ,IsComputed)

    exec sp_executesql @sql,
                       N'@TableName varchar(50),@schema varchar(50)',
                       @[email protected],@[email protected]            
    /*
    SELECT @DBName--DB_NAME()
            ,TABLE_SCHEMA
            ,TABLE_NAME
            ,COLUMN_NAME
            ,CAST(ORDINAL_POSITION AS INT)
            ,COLUMN_DEFAULT
            ,dobj.name AS ColumnDefaultName
            ,CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END
            ,DATA_TYPE
            ,CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
            ,CAST(NUMERIC_PRECISION AS INT)
            ,CAST(NUMERIC_SCALE AS INT)
            ,DOMAIN_NAME
            ,COLUMN_NAME + ','
            ,'' AS FieldDefinition
            ,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
            ,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
            ,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
            ,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn 
            FROM INFORMATION_SCHEMA.COLUMNS c
            JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
            LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
            JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
            LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'

    WHERE c.TABLE_NAME = @TableName
    ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
    */
    SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields

    INSERT INTO @HoldingArea (Flds) VALUES('(')

    INSERT INTO @Definition(FieldValue)VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @DBName + '.' + @NewTableSchema + '.' + @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)
    INSERT INTO @Definition(FieldValue)VALUES('(')
    INSERT INTO @Definition(FieldValue)
    SELECT   CHAR(10) + FieldName + ' ' + 
        --CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END ELSE UPPER(DataType) +CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END END + CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END 

        CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + 
            CASe WHEN IsNullable = 1 THEN ' NULL ' 
            ELSE ' NOT NULL ' 
            END 
        ELSE 
            case when IsComputed is null then
                UPPER(DataType) +
                CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' 
                ELSE 
                    CASE WHEN DataType = 'numeric' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' 
                    ELSE
                        CASE WHEN DataType = 'decimal' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' 
                        ELSE '' 
                        end  
                    end 
                END +
                CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' 
                ELSE '' 
                END +
                CASE WHEN IsNullable = 1 THEN ' NULL ' 
                ELSE ' NOT NULL ' 
                END +
                CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + replace(ColumnDefaultName,@TableName,@NewTableName) + '] DEFAULT' + UPPER(ColumnDefaultValue) 
                ELSE '' 
                END 
            else
                ' as '+IsComputed+' '
            end
        END + 
        CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' 
        ELSE ',' 
        END 

    FROM    @ShowFields

    IF @IncludeConstraints = 1
        BEGIN    

        set @sql=
        '
        use '[email protected]+'
        SELECT  distinct  '',CONSTRAINT ['' + @NewTableName+''_''+replace(name,@TableName,'''') + ''] FOREIGN KEY ('' + ParentColumns + '') REFERENCES ['' + ReferencedObject + '']('' + ReferencedColumns + '')'' 
           FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
                ,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + '',''   
                FROM   sys.foreign_key_columns fkc   
                JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id   
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ParentColumns,   
                REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + '',''   
                FROM   sys.foreign_key_columns fkc  
                JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ReferencedColumns   
                FROM sys.foreign_keys fk    
                    inner join sys.schemas s on fk.schema_id=s.schema_id and [email protected]) a    
            WHERE ParentObject = @TableName    
        '

        print @sql

        INSERT INTO @Definition(FieldValue)
        exec sp_executesql @sql,
                   N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)',
                       @[email protected],@[email protected],@[email protected]
            /*
           SELECT    ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'  
           FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
                ,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + ','   
                FROM   sys.foreign_key_columns fkc   
                JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id   
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('')   )), 2, 8000)) ParentColumns,   
                REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + ','   
                FROM   sys.foreign_key_columns fkc  
                JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('')   )), 2, 8000)) ReferencedColumns   
                FROM sys.foreign_keys fk    ) a    
            WHERE ParentObject = @TableName    
            */

            set @sql=
            '
            use '[email protected]+'
            SELECT distinct '',CONSTRAINT ['' + @NewTableName+''_''+replace(c.name,@TableName,'''') + ''] CHECK '' + definition 
            FROM sys.check_constraints c join sys.schemas s on c.schema_id=s.schema_id and [email protected]    
            WHERE OBJECT_NAME(parent_object_id) = @TableName
            '

            print @sql
            INSERT INTO @Definition(FieldValue) 
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)',
                       @[email protected],@[email protected],@[email protected]
            /*
            SELECT ',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints    
            WHERE OBJECT_NAME(parent_object_id) = @TableName
            */

            set @sql=
            '
            use '[email protected]+'
            SELECT DISTINCT  PKObject = cco.object_id 
            FROM    sys.key_constraints cco    
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            join sys.schemas s on cco.schema_id=s.schema_id and [email protected]
            WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
            '
            print @sql
            INSERT INTO @PKObjectID(ObjectID) 
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@schema varchar(50)',
                               @[email protected],@[email protected]
            /*
            SELECT DISTINCT  PKObject = cco.object_id 
            FROM    sys.key_constraints cco    
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id    
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
            */

            set @sql=
            '
            use '[email protected]+'
            SELECT DISTINCT    PKObject = cco.object_id
            FROM    sys.key_constraints cco   
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            join sys.schemas s on cco.schema_id=s.schema_id and [email protected]
            WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
            '
            print @sql
            INSERT INTO @Uniques(ObjectID)
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@schema varchar(50)',
                               @[email protected],@[email protected]
            /*
            SELECT DISTINCT    PKObject = cco.object_id
            FROM    sys.key_constraints cco   
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
            */

            SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

            declare @t TableType
            insert @t select * from @PKObjectID
            declare @u TableType
            insert @u select * from @Uniques

            set @sql=
            '
            use '[email protected]+'
            SELECT distinct '',CONSTRAINT '' + @NewTableName+''_''+replace(cco.name,@TableName,'''') + CASE type WHEN ''PK'' THEN '' PRIMARY KEY '' + CASE WHEN pk.ObjectID IS NULL THEN '' NONCLUSTERED '' ELSE '' CLUSTERED '' END  WHEN ''UQ'' THEN '' UNIQUE '' END + CASE WHEN u.ObjectID IS NOT NULL THEN '' NONCLUSTERED '' ELSE '''' END 
            + ''(''+REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''    
            FROM   sys.key_constraints ccok   
            LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
            LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id 
            LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
            WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    
            order by key_ordinal FOR XML PATH(''''))), 2, 8000)) + '')''
            FROM sys.key_constraints cco 
            inner join sys.schemas s on cco.schema_id=s.schema_id and [email protected]
            LEFT JOIN @U u ON cco.object_id = u.objectID
            LEFT JOIN @t pk ON cco.object_id = pk.ObjectID    
            WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName 

            '

            print @sql
            INSERT INTO @Definition(FieldValue)
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50),@t TableType readonly,@u TableType readonly',
                               @[email protected],@[email protected],@[email protected],@[email protected],@[email protected]

            /*
            SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END  WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END 
            + '(' +REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','    
            FROM   sys.key_constraints ccok   
            LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
           LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id 
           LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
           WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    FOR XML PATH(''))), 2, 8000)) + ')'
           FROM sys.key_constraints cco 
           LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID    
           LEFT JOIN @Uniques u ON cco.object_id = u.objectID
           WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName 
           */
        END

        INSERT INTO @Definition(FieldValue) VALUES(')')

        set @sql=
        '
        use '[email protected]+'
        select '' on '' + d.name + ''([''+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)
                          join sys.schemas s on t.schema_id=s.schema_id
                          join sys.data_spaces d on i.data_space_id=d.data_space_id
        where [email protected] and [email protected]
        order by key_ordinal
        '

        print 'x'
        print @sql
        INSERT INTO @Definition(FieldValue) 
        exec sp_executesql @sql,
                           N'@TableName varchar(50),@schema varchar(50)',
                           @[email protected],@[email protected]

        IF @IncludeIndexes = 1
        BEGIN
            set @sql=
            '
            use '[email protected]+'
            SELECT distinct '' CREATE '' + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '[email protected]+'.'[email protected]+'.'[email protected]+' ('' 
            +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''   
            FROM  sys.index_columns sc  
            JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
            WHERE  [email protected] AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                         and is_included_column=0
            ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000)) + '')''+
            ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE((   SELECT name + '',''   
            FROM  sys.index_columns sc  
            JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
            WHERE  [email protected] AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                                         and is_included_column=1
            ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000))+'')'' ,'''')+''''    
            FROM sys.indexes i join sys.tables t on i.object_id=t.object_id
                               join sys.schemas s on t.schema_id=s.schema_id   
            AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 
                where [email protected] and [email protected]
            '

            print @sql
            INSERT INTO @Definition(FieldValue)    
            exec sp_executesql @sql,
                               N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit',
                               @[email protected],@[email protected],@[email protected],@[email protected]

        END 

            /*

                SELECT   'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' +  OBJECT_NAME(object_id) + '] (' +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','   
                FROM  sys.index_columns sc  
                JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id   
                WHERE  OBJECT_NAME(sc.object_id) = @TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id   
                ORDER BY index_column_id ASC   FOR XML PATH('')    )), 2, 8000)) + ')'    
                FROM sys.indexes i    
                WHERE   OBJECT_NAME(object_id) = @TableName
                AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0 

            */

            INSERT INTO @MainDefinition(FieldValue)   
            SELECT FieldValue FROM @Definition    
            ORDER BY DefinitionID ASC 

            ----------------------------------
            --SELECT FieldValue+'' FROM @MainDefinition FOR XML PATH('')
            set @script='use '[email protected]+' '+(SELECT FieldValue+'' FROM @MainDefinition FOR XML PATH(''))

            --declare @q    varchar(max)
            --set @q=(select replace((SELECT FieldValue FROM @MainDefinition FOR XML PATH('')),'</FieldValue>',''))
            --set @script=(select REPLACE(@q,'<FieldValue>',''))
            --drop type TableType
END try
-- ##############################################################################################################################################################################
BEGIN CATCH        
    BEGIN
        -- INIZIO  Procedura in errore =========================================================================================================================================================
            PRINT '***********************************************************************************************************************************************************' 
            PRINT 'ErrorNumber               : ' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX))
            PRINT 'ErrorSeverity             : ' + CAST(ERROR_SEVERITY() AS NVARCHAR(MAX)) 
            PRINT 'ErrorState                : ' + CAST(ERROR_STATE() AS NVARCHAR(MAX)) 
            PRINT 'ErrorLine                 : ' + CAST(ERROR_LINE() AS NVARCHAR(MAX)) 
            PRINT 'ErrorMessage              : ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX))
            PRINT '***********************************************************************************************************************************************************' 
        -- FINE  Procedura in errore =========================================================================================================================================================
    END 
        set @script=''
    return -1
END CATCH   
-- ##############################################################################################################################################################################   

um es auszuführen:

declare @s varchar(max)
exec [util_ScriptTable]   'db','schema_source','table_source',1,1,'schema_dest','tab_dest',0,@s output
select @s






sql group-by aggregate-functions