MS SQL stored procedure to populate `geometry_columns` table. This table is used by QGIS to identify tables with spatial data.

populate_geometry_columns_table.sql

-- =============================================
-- Author:      Chris Tippett
-- Create date: 2014-08-12
-- Description: Detect columns with geometry datatypes and add them to [dbo].[geometry_columns]
-- =============================================
CREATE PROCEDURE [dbo].[Populate_Geometry_Columns] @schema VARCHAR(MAX) = '', @table VARCHAR(MAX) = ''
AS
BEGIN
    SET NOCOUNT ON;
   
 DECLARE
  @db_name VARCHAR(MAX)
  ,@tbl_schema VARCHAR(MAX)
  ,@tbl_name VARCHAR(MAX)
  ,@tbl_oldname VARCHAR(MAX)
  ,@clm_name VARCHAR(MAX)
  ,@geom_srid INT
  ,@geom_type VARCHAR(MAX)
  ,@msg VARCHAR(MAX)
  
 SET @msg = '--------------------------------------------------'+CHAR(10)
 SET @msg += 'FINDING GEOMETRY DATATYPES'
 RAISERROR(@msg,0,1) WITH NOWAIT
  
 -- check whether [dbo].[geometry_columns] exists and create it if necessary
 SET @msg = ' > Checking whether table [dbo].[geometry_columns] exists'
 RAISERROR(@msg,0,1) WITH NOWAIT
  
 IF OBJECT_ID(DB_NAME()+'.dbo.geometry_columns') IS NULL
  BEGIN
   SET @msg = '    - Table does not exist, creating it now'
  
   CREATE TABLE [dbo].[geometry_columns] (
       [f_table_catalog] [varchar](128) NOT NULL
       ,[f_table_schema] [varchar](128) NOT NULL
       ,[f_table_name] [varchar](256) NOT NULL
       ,[f_geometry_column] [varchar](256) NOT NULL
       ,[coord_dimension] [int] NOT NULL
       ,[srid] [int] NOT NULL
       ,[geometry_type] [varchar](30) NOT NULL
    CONSTRAINT [geometry_columns_pk] PRIMARY KEY CLUSTERED (
        [f_table_catalog] ASC
        ,[f_table_schema] ASC
        ,[f_table_name] ASC
        ,[f_geometry_column] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY]
  END
 ELSE
  SET @msg = '    - Table already exists, no further action necessary'
  
 RAISERROR(@msg,0,1) WITH NOWAIT
  
 SET @schema = NULLIF(@schema,'')
 SET @table = NULLIF(@table,'')
  
 -- setup temporary table to contain the SRID and type of geometry
 CREATE TABLE #geom_info (SRID INT, GEOM_TYPE VARCHAR(50), Count_Type INT)
  
 DECLARE column_cursor CURSOR FOR
  SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE
   DATA_TYPE = 'geometry'
   AND TABLE_CATALOG = DB_NAME()
   AND TABLE_SCHEMA LIKE COALESCE(@schema,'%')
   AND TABLE_NAME LIKE COALESCE(@table,'%')
  ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
      
 OPEN column_cursor
 FETCH NEXT FROM column_cursor INTO @db_name, @tbl_schema, @tbl_name, @clm_name
  
 SET @msg = ' > Searching  ['+@db_name+'].['+@tbl_schema+'].['+@tbl_name+'] for geometry columns'
 RAISERROR(@msg,0,1) WITH NOWAIT
  
 IF @@FETCH_STATUS < 0
  BEGIN
   SET @msg = '    - No columns with geometry datatype found'
   RAISERROR(@msg,0,1) WITH NOWAIT
  END
  
 WHILE @@FETCH_STATUS = 0
  BEGIN
     
   -- check whether column exists already in [geometry_columns]
   IF EXISTS (
    SELECT 1
    FROM dbo.geometry_columns
    WHERE
     [f_table_catalog] = @db_name AND
     [f_table_schema] = @tbl_schema AND
     [f_table_name] = @tbl_name AND
     [f_geometry_column] = @clm_name
   )
    BEGIN
     SET @msg = '    - Geometry column "'+@clm_name+'" found and already exists in geometry_columns table'
     RAISERROR(@msg,0,1) WITH NOWAIT
  
    END
  
   ELSE
    BEGIN
      -- use dynamic sql to get srid and geometry type
     INSERT INTO
      #geom_info
     EXEC('
     SELECT
      ['+@clm_name+'].STSrid AS SRID
      ,['+@clm_name+'].MakeValid().STGeometryType() AS GEOM_TYPE
      ,COUNT(*) AS Count_Type
     FROM
      ['+@db_name+'].['+@tbl_schema+'].['+@tbl_name+']
     WHERE
      ['+@clm_name+'].STIsValid() = 1
     GROUP BY
      ['+@clm_name+'].STSrid
      ,['+@clm_name+'].MakeValid().STGeometryType()
     ')
    
     IF @@ROWCOUNT > 1
      BEGIN
       SET @msg = '    - WARNING: More than 1 geometry type detected in column. Taking most frequent type for column definition'
       RAISERROR(@msg,0,1) WITH NOWAIT
      END
 
     -- assign srid and geometry type to variables
     SELECT TOP 1
      @geom_srid = SRID
      ,@geom_type = UPPER(GEOM_TYPE)
     FROM
      #geom_info
     ORDER BY
      Count_Type DESC
  
     -- reset @geom_info contents
     DELETE FROM #geom_info
 
     -- insert into [geometry_columns] if the column doesn't already exist
     SET @msg = '    - Adding column "'+@clm_name+'" to geometry_columns table'+CHAR(10)
     SET @msg += '       + geometry type: '+@geom_type+CHAR(10)
     SET @msg += '       + srid: '+CAST(@geom_srid AS VARCHAR(10))
     RAISERROR(@msg,0,1)
  
     INSERT INTO dbo.geometry_columns
     VALUES (@db_name, @tbl_schema, @tbl_name, @clm_name, 2, @geom_srid, @geom_type)
  
    END
  
   -- iterate cursor
   FETCH NEXT FROM column_cursor INTO @db_name, @tbl_schema, @tbl_name, @clm_name
  
   -- check whether the cursor is looping through another column of the previous table (purely for messaging purposes)
   IF @tbl_name <> @tbl_oldname
    BEGIN
     SET @msg = ' > Searching  ['+@db_name+'].['+@tbl_schema+'].['+@tbl_name+'] for geometry columns'
     RAISERROR(@msg,0,1) WITH NOWAIT
    END
   SET @tbl_oldname = @tbl_name
  
  END
  
  CLOSE column_cursor
  DEALLOCATE column_cursor
  
  SET @msg = '--------------------------------------------------'+CHAR(10)
  SET @msg += 'Done!'+CHAR(10)
  SET @msg += '--------------------------------------------------'
  RAISERROR(@msg,0,1) WITH NOWAIT
END

Sweet! I was about to create a similar script and this saves me a lot of time. Thanks for posting it!

EDIT: I made a minor revision in a fork, consider updating your Gist if you like it: https://gist.github.com/MattiasSp/d4527ea708d432bd1eb926fb7e029291/revisions#diff-ab1a43887de43b5bfb059cb689cc75d4

This is great. Thanks!

This is great. Thanks!

A blast from the past! You're very welcome, glad it could be of some help.

Sweet! I was about to create a similar script and this saves me a lot of time. Thanks for posting it!

EDIT: I made a minor revision in a fork, consider updating your Gist if you like it: https://gist.github.com/MattiasSp/d4527ea708d432bd1eb926fb7e029291/revisions#diff-ab1a43887de43b5bfb059cb689cc75d4

Thanks @MattiasSp, I've incorporated your changes :)

Hi, thanks for the code! I have a question regarding your script. I am newby in QGIS + MS SQL, I am trying to execute your script with right click on connected DB, then "execute SQL" but after copy pasting and running your code in the SQL window nothing happens. I do not see the geometry_columns table in the dbo schema. Any idea what am I doing wrong? the code itself runs OK without any errors.

@MaciejSzypulski, the included code creates a stored procedure, you need to execute the stored procedure for it to create and populate the geometry_columns table.

Try running this SQL snippet: EXEC [dbo].[Populate_Geometry_Columns];