Uninstall SQL Server SSMS 2016 from Windows 7 / Server 2008 R2

  1. Download the SSMS uninstall powershell script

  2. Download the Get-RemoteProgram Powershell script and make sure to place it in the same directory as the Powershell uninstall script above.

  3. Open an admin session of Powershell

  4. In powershell, run the command "Get-ExecutionPolicy". This command will display the current ExecutionPolicy of your machine. You will need to change this value if it is not "Unrestricted".

     
  5. In powershell, run the command "Set-ExecutionPolicy -ExecutionPolicy Unrestricted". This will launch a popup asking you to confirm changing the ExecutionPolicy. Confirm the change.

  6. Navigate to the folder with the SSMS uninstall Powershell script and run ".\UninstallSsms.ps1". This will launch a popup asking you to confirm launching the script. Click run. The script will now uninstall SSMS and all its dependencies.

     
  7. To revert the ExecutionPolicy back to its original state, run the command "Set-ExecutionPolicy -ExecutionPolicy [previous policy value]`". This will launch a popup asking you confirm changing the ExecutionPolicy. Confirm the change.

T-SQL, создание PK и FK в базе данных на основе структуры другой БД

Необходимо включить режим SQLCMD в Management Studio.

:setvar db_src "src"
:setvar db_dst "dst"

DECLARE @create_scripts TABLE
  (
     id     INT IDENTITY,
     script NVARCHAR(max)
  )
DECLARE @sql NVARCHAR(max)

BEGIN TRANSACTION recreate_constraints

BEGIN try
    USE $(db_src)

    INSERT INTO @create_scripts
    SELECT 'alter table [' + x.[table]
           + '] add constraint ' + x.pk_name
           + ' primary key clustered (' + x.pk_fields
           + ') ON [PRIMARY]'
    FROM   (SELECT kc.NAME                                      AS pk_name,
                   Object_name(kc.parent_object_id)             AS [table],
                   (SELECT Stuff((SELECT ',' + [column] + ' ' + sort
                                  FROM   (SELECT kcu.column_name      AS
                                                 'column',
                                                 kcu.ordinal_position AS
                                                 'position',
                                                 CASE
                                                   WHEN ic.is_descending_key = 1
                                                 THEN
                                                   'DESC'
                                                   ELSE 'ASC'
                                                 END                  AS 'sort'
                                          FROM
                                 information_schema.key_column_usage
                                 kcu
                                 INNER JOIN
    information_schema.table_constraints tc
            ON tc.table_name = kcu.table_name
               AND tc.constraint_name =
                   kcu.constraint_name
               AND tc.constraint_type =
                   'PRIMARY KEY'
    INNER JOIN sys.index_columns ic
            ON ic.object_id =
               Object_id(kcu.table_name)
               AND ic.index_column_id =
                   kcu.ordinal_position
    WHERE  kcu.table_name =
    Object_name(kc.parent_object_id)
    AND kcu.constraint_name = kc.NAME) cte
    ORDER  BY position
    FOR xml path('')), 1, 1, '')) AS pk_fields
    FROM   sys.key_constraints kc
    WHERE  kc.[type] = 'PK') x
    ORDER  BY x.[table] ASC

    USE $(db_dst)

    DECLARE @i INT

    SET @i = (SELECT Min(id)
              FROM   @create_scripts);

    WHILE EXISTS(SELECT TOP 1 1
                 FROM   @create_scripts
                 WHERE  id = @i)
      BEGIN
          SET @sql = (SELECT TOP 1 script
                      FROM   @create_scripts
                      WHERE  id = @i);

          EXEC(@sql);

          SET @i = @i + 1;
      END

    PRINT 'Primary keys done!'

    DELETE FROM @create_scripts

    USE $(db_src)

    INSERT INTO @create_scripts
    SELECT ' ALTER TABLE ' + const.parent_obj
           + ' WITH CHECK ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY ( '
           + const.parent_col_csv + ' ) REFERENCES '
           + const.ref_obj + '(' + const.ref_col_csv
           + ') ALTER TABLE ' + const.parent_obj
           + ' CHECK CONSTRAINT ' + const.const_name
    FROM   (SELECT Quotename(fk.NAME)                                 AS
                   [const_name],
                   Quotename(schParent.NAME) + '.'
                   + Quotename(Object_name(fkc.parent_object_id))     AS
                   [parent_obj],
                   Stuff((SELECT ','
                                 + Quotename(Col_name(fcP.parent_object_id,
                                 fcp.parent_column_id))
                          FROM   sys.foreign_key_columns AS fcP
                          WHERE  fcp.constraint_object_id = fk.object_id
                          FOR xml path('')), 1, 1, '')                AS
                   [parent_col_csv],
                   Quotename(schRef.NAME) + '.'
                   + Quotename(Object_name(fkc.referenced_object_id)) AS
                   [ref_obj],
                   Stuff((SELECT ','
                                 + Quotename(Col_name(fcR.referenced_object_id,
                                       fcR.referenced_column_id))
                          FROM   sys.foreign_key_columns AS fcR
                          WHERE  fcR.constraint_object_id = fk.object_id
                          FOR xml path('')), 1, 1, '')                AS
                   [ref_col_csv]
            FROM   sys.foreign_key_columns AS fkc
                   INNER JOIN sys.foreign_keys AS fk
                           ON fk.object_id = fkc.constraint_object_id
                   INNER JOIN sys.objects AS oParent
                           ON oParent.object_id = fkc.parent_object_id
                   INNER JOIN sys.schemas AS schParent
                           ON schParent.schema_id = oParent.schema_id
                   INNER JOIN sys.objects AS oRef
                           ON oRef.object_id = fkc.referenced_object_id
                   INNER JOIN sys.schemas AS schRef
                           ON schRef.schema_id = oRef.schema_id
            GROUP  BY fkc.parent_object_id,
                      fkc.referenced_object_id,
                      fk.NAME,
                      fk.object_id,
                      schParent.NAME,
                      schRef.NAME) AS const
    ORDER  BY const.const_name

    USE $(db_dst)

    SET @i = (SELECT Min(id)
              FROM   @create_scripts);

    WHILE EXISTS(SELECT TOP 1 1
                 FROM   @create_scripts
                 WHERE  id = @i)
      BEGIN
          SET @sql = (SELECT TOP 1 script
                      FROM   @create_scripts
                      WHERE  id = @i);

          EXEC(@sql);

          SET @i = @i + 1;
      END

    PRINT 'Foreign keys done!'

    DELETE FROM @create_scripts

    COMMIT TRANSACTION recreate_constraints;

    PRINT '----'

    PRINT 'Ok'
END try

BEGIN catch
    ROLLBACK TRANSACTION recreate_constraints;

    PRINT 'Error: ' + Error_message()
END catch