locked
Replace particular line in a huge text file; RRS feed

  • Question

  • I have a .sql script with create database statements. I need to replace any line containing the word COLLATE .. with FOR ATTACH;
     The files content looks like this.

    CREATE DATABASE [AdminDB] ON  PRIMARY 
    ( NAME = N'AdminDB', FILENAME = N'D:\datafiles\AdminDB.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
     LOG ON 
    ( NAME = N'AdminDB_log', FILENAME = N'D:\logfiles\\SQLLog\AdminDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
     COLLATE SQL_Latin1_General_CP1_CI_AS ---- THIS LINE SHOULD BE (FOR ATTACH;)
    ALTER DATABASE [AdminDB] ADD FILEGROUP [FG_Backup]
    ALTER DATABASE [AdminDB] ADD FILEGROUP [FG_Maintenance]
    ALTER DATABASE [AdminDB] SET COMPATIBILITY_LEVEL = 100
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [AdminDB].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    ALTER DATABASE [AdminDB] SET ANSI_NULL_DEFAULT OFF 
    ALTER DATABASE [AdminDB] SET ANSI_NULLS OFF 
    ALTER DATABASE [AdminDB] SET ANSI_PADDING OFF  
    ALTER DATABASE [AdminDB] SET DB_CHAINING OFF 
    CREATE DATABASE [MonitoringDB_895] ON  PRIMARY 
    ( NAME = N'MonitoringDB_data', FILENAME = N'D:\mount\data3\SQLDATA\MonitoringDB_data.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
    ( NAME = N'MonitoringDB_data2', FILENAME = N'D:\mount\data4\SQLDATA\MonitoringDB_data2.ndf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
     LOG ON 
    ( NAME = N'MonitoringDB_log', FILENAME = N'D:\logfiles\\SQLLog\MonitoringDB_Log.ldf' , SIZE = 104704KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
     COLLATE Latin1_General_CP1_CS_AS---- THIS LINE SHOULD BE (FOR ATTACH)
    ALTER DATABASE [MonitoringDB_895] SET COMPATIBILITY_LEVEL = 100
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [MonitoringDB_895].[dbo].[sp_fulltext_database] @action = 'disable'
    end
    ....

    Thanks

    Tuesday, May 20, 2014 9:21 PM

Answers

  • Get-Content sqlfile.sql | %{$_ -replace 'COLLATE','FOR GOOBERS;'} | Out-File newsql.sql


    ¯\_(ツ)_/¯


    • Edited by jrv Tuesday, May 20, 2014 9:29 PM
    • Marked as answer by SQL_Jay Wednesday, May 21, 2014 2:33 PM
    Tuesday, May 20, 2014 9:24 PM
  • So just replace the whole line.


    ¯\_(ツ)_/¯

    • Marked as answer by SQL_Jay Wednesday, May 21, 2014 2:33 PM
    Tuesday, May 20, 2014 9:34 PM

All replies

  • Get-Content sqlfile.sql | %{$_ -replace 'COLLATE','FOR GOOBERS;'} | Out-File newsql.sql


    ¯\_(ツ)_/¯


    • Edited by jrv Tuesday, May 20, 2014 9:29 PM
    • Marked as answer by SQL_Jay Wednesday, May 21, 2014 2:33 PM
    Tuesday, May 20, 2014 9:24 PM
  • Thanks unfortunately this is doing this
    ...
    FILEGROWTH = 10%)
     FOR ATTACH; SQL_Latin1_General_CP1_CI_AS

    ....

    FILEGROWTH = 10%)
     FOR ATTACH; Latin1_General_CP1_CS_AS

    I Need it like this

    FILEGROWTH = 10%)
     FOR ATTACH; 

    ....

    FILEGROWTH = 10%)
     FOR ATTACH; 

    Thanks



    Tuesday, May 20, 2014 9:31 PM
  • So just replace the whole line.


    ¯\_(ツ)_/¯

    • Marked as answer by SQL_Jay Wednesday, May 21, 2014 2:33 PM
    Tuesday, May 20, 2014 9:34 PM
  • Thanks
    Wednesday, May 21, 2014 2:33 PM