How to export one table or sheet to multiple tables or sheets

If you have one excel sheet or table and you want to export it to multiple sheets or tables use the following steps:

1. Specify the column -tablesNamesColumn- you want its data to be the names of the tables/sheets 
2. import the main sheet to MSSQL 
3. Select distinct values from the tablesNamesColumn
4. Create a table and name it "TablesNames":                                                                                       CREATE TABLE [dbo].[TablesNames](
          [id] [int] IDENTITY(1,1) PRIMARY KEY,
          [Table_Name] [nvarchar](255) NOT NULL,
   )   
5. insert the tables names in the table which contains the tables names                                               insert into TablesNames
   SELECT distinct tablesNamesColumn  
   FROM [AllSheets]
6. Now you need to loop inside the tables names and create tables in the same format         
   DECLARE @LoopCounter INT , @MaxTablesNamesId INT,
           @TableName NVARCHAR(100)
   SELECT @LoopCounter = 0 , @MaxTablesNamesId = count(*)
   FROM TablesNames
   
   
   WHILE ( @LoopCounter IS NOT NULL
           AND  @LoopCounter <= @MaxTablesNamesId)
   BEGIN
      SELECT @TableName = Table_Name FROM TablesNames
      WHERE id = @LoopCounter
     -- PRINT @TableName 
      SELECT @LoopCounter  = min(id) FROM TablesNames
      WHERE id > @LoopCounter
     
   declare @q nvarchar(max) = '
   drop table ['+@TableName+'];
   SELECT * INTO  ['+@TableName+']
   FROM [delme]
   WHERE Institution = N'''+ @TableName +''';' 
   exec (@q)
   END
7. Use the following steps to export the data to Excel 97-2003 file, because I tried Excel 2010 and 2016, the only one works fine is 97-2003  
   

mssql search in all tables in

-- To search in all tables content and return a list of tables where the value

-- has been founded

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER   PROCEDURE [dbo].[SearchAllTables]

    @SearchStr NVARCHAR(100)

AS

BEGIN

    -- Create results table with NVARCHAR to support Arabic characters

    CREATE TABLE #Results (

        TableName NVARCHAR(370),

        ColumnName NVARCHAR(128),

        ColumnValue NVARCHAR(MAX),

        PrimaryKeyColumn NVARCHAR(128),

        PrimaryKeyValue NVARCHAR(500)

    )

   

    SET NOCOUNT ON

   

    DECLARE @TableName NVARCHAR(256),

            @ColumnName NVARCHAR(128),

            @SearchStr2 NVARCHAR(110),

            @PKColumn NVARCHAR(128),

            @SQL NVARCHAR(MAX)

   

    SET @TableName = ''

    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

   

    WHILE @TableName IS NOT NULL

    BEGIN

        SET @ColumnName = ''

        SET @TableName = (

            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

            FROM INFORMATION_SCHEMA.TABLES

            WHERE TABLE_TYPE = 'BASE TABLE'

                AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

                AND OBJECTPROPERTY(

                    OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),

                    'IsMSShipped'

                ) = 0

        )

       

        -- Get primary key column for current table

        IF @TableName IS NOT NULL

        BEGIN

            SET @PKColumn = (

                SELECT TOP 1 QUOTENAME(cu.COLUMN_NAME)

                FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu

                    ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME

                    AND tc.TABLE_SCHEMA = cu.TABLE_SCHEMA

                    AND tc.TABLE_NAME = cu.TABLE_NAME

                WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'

                    AND tc.TABLE_SCHEMA = PARSENAME(@TableName, 2)

                    AND tc.TABLE_NAME = PARSENAME(@TableName, 1)

                ORDER BY cu.ORDINAL_POSITION

            )

        END

       

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        BEGIN

            SET @ColumnName = (

                SELECT MIN(QUOTENAME(COLUMN_NAME))

                FROM INFORMATION_SCHEMA.COLUMNS

                WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

                    AND TABLE_NAME = PARSENAME(@TableName, 1)

                    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')

                    AND QUOTENAME(COLUMN_NAME) > @ColumnName

            )

           

            IF @ColumnName IS NOT NULL

            BEGIN

                -- Build dynamic SQL with NVARCHAR casting to preserve Arabic characters

                SET @SQL = N'

                    INSERT INTO #Results (TableName, ColumnName, ColumnValue, PrimaryKeyColumn, PrimaryKeyValue)

                    SELECT

                        ''' + @TableName + ''',

                        ''' + @ColumnName + ''',

                        LEFT(CAST(' + @ColumnName + ' AS NVARCHAR(MAX)), 3630),

                        ' + CASE WHEN @PKColumn IS NOT NULL

                                THEN '''' + @PKColumn + ''''

                                ELSE 'NULL'

                           END + ',

                        ' + CASE WHEN @PKColumn IS NOT NULL

                                THEN 'CAST(' + @PKColumn + ' AS NVARCHAR(500))'

                                ELSE 'NULL'

                           END + '

                    FROM ' + @TableName + ' WITH (NOLOCK)

                    WHERE CAST(' + @ColumnName + ' AS NVARCHAR(MAX)) LIKE N' + @SearchStr2

               

                EXEC sp_executesql @SQL

            END

        END

    END

   

    SELECT

        TableName,

        ColumnName,

        ColumnValue,

        PrimaryKeyColumn,

        PrimaryKeyValue

    FROM #Results

    ORDER BY TableName, ColumnName

   

    DROP TABLE #Results

END

GO

Clear SDE schema from tables of dropped schemas

delete from column_registry
where OWNER in ('FLEET_V1','GIS_B','ATLAS_V1', 'GIS_V1', 'GIS','gis', 'GIS_CODING', 'GIS_TEST', 'GIS_C', 'gis_c', 'GIS_D', 'gis_d'  );

delete from GEOMETRY_COLUMNS
where F_TABLE_SCHEMA in ('FLEET_V1','GIS_B','ATLAS_V1', 'GIS_V1', 'GIS','gis', 'GIS_CODING', 'GIS_TEST', 'GIS_C', 'gis_c', 'GIS_D', 'gis_d'  );

delete from LAYERS
where OWNER in ('FLEET_V1','GIS_B','ATLAS_V1', 'GIS_V1', 'GIS','gis', 'GIS_CODING', 'GIS_TEST', 'GIS_C', 'gis_c', 'GIS_D', 'gis_d' );

delete from GDB_ITEMS
where NAME like 'FLEET_V1%' or NAME like'GIS_B%' or NAME like'ATLAS_V1%' or  NAME like'GIS_V1%' or NAME like'GIS%' or NAME like'GIS_CODING%'
or NAME like'gis_c%' or NAME like'gis_d%' or NAME like'gis%' or NAME like'GIS_D%' ;

COMMIT;

DROP USER gis_d CASCADE;
DROP USER gis_c CASCADE;
DROP USER gis_test CASCADE;
DROP USER gis CASCADE;

DECLARE

  match_count integer;

  v_search_string varchar2(4000) := 'fff';

BEGIN  

  FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP   

    EXECUTE IMMEDIATE    

      'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||

      ' WHERE '||t.column_name||' = :1'   

       INTO match_count  

      USING v_search_string; 

    IF match_count > 0 THEN 

      dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );

    END IF; 

  END LOOP;

END;