Get all fields names for all tables in SQL

,

I recently worked on a project that needs to convert Notes database to a web project. The first thing is to export data from Notes DB to SQL. Since Notes is NOT a relationship database, after the export, I found the column names are very confusing, so the task becomes how to interpret the relationship between the columns. I decided that it is neccesary to find out every column name in every table and start the analysis from there. I was able to write a script to generate all the columns names and their tables names using this script, I hope you find it helpful.

USE [SH]
GO

/****** Object:  StoredProcedure [dbo].[spGetAllFieldsNamesInAllTables]    Script Date: 06/19/2013 15:13:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  proc [dbo].[spGetAllFieldsNamesInAllTables] 
AS
--this sp will go through all the sp and fn and grant exec to @Role 
declare @TableName varchar(100)
declare @ColumnName varchar(100)

declare @sql varchar(1000)

if not exists (select * from sys.objects where [name] = 'TableColumns')
	create table TableColumns
	(
		[TableName] varchar(100),
		[ColumnName] varchar(100)
	)

	declare c cursor for
		SELECT name
		FROM sys.objects
		WHERE type_desc LIKE '%TABLE%' and Type = 'U'
		order by [name]
		
	OPEN c
	FETCH NEXT FROM c 
	INTO @TableName
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
	set @sql = '
		insert into TableColumns (TableName, ColumnName)
		select ' + '''' + @TableName + '''' + ', column_name from information_schema.columns
		where table_name = ' + '''' + @TableName + '''' 
		+ ' order by ordinal_position '

		
 
		exec (@sql)


	  FETCH NEXT FROM c 
	    INTO @TableName
	    
	END
	CLOSE c
	DEALLOCATE c

	

GO

Rate

3.67 (3)

Share

Share

Rate

3.67 (3)