[dba-SQLServer] Test for empty columns

This sproc gets at all the columns in all the tables:

 
CREATE  procedure [dbo].[_DataDictionary]
--Create on the fly data dictionary.
as
-build your destination table if necessary
IF NOT EXISTS (Select name FROM sys.objects WHERE object_id = OBJECT_ID(N'[DataDictionary]') AND type in (N'U'))
		BEGIN
		CREATE TABLE [dbo].[DataDictionary](
			[table_qualifier] [varchar](50) NULL,
			[table_owner] [varchar](50) NULL,
			[table_name] [varchar](50) NOT NULL,
			[column_name] [varchar](50) NOT NULL,
			[data_type] [int] NULL,
			[type_name] [varchar](50) NULL,
			[precision] [varchar](50) NULL,
			[length] [varchar](50) NULL,
			[scale] [varchar](50) NULL,
			[radix] [varchar](50) NULL,
			[nullable] [varchar](50) NULL,
			[remarks] [varchar](50) NULL,
			[column_def] [varchar](50) NULL,
			[sql_data_type] [varchar](50) NULL,
			[sql_datetime_sub] [varchar](50) NULL,
			[char_octet_length] [varchar](50) NULL,
			[ordinal_position] [varchar](50) NULL,
			[is_nullable] [varchar](50) NULL,
			[ss_data_type] [varchar](50) NULL,
		 CONSTRAINT [PK_DataDictionary] PRIMARY KEY CLUSTERED 
		(
			[table_name] ASC,
			[column_name] ASC
		)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
		) ON [PRIMARY]
		END
-but if the destination already exists, just empty it
truncate table datadictionary

-select all the object names in the database into a cursor 

DECLARE tnames_cursor CURSOR
FOR
  --2000 version: select   name, type from sysobjects where (type = 'v' or type = 'u') order by name 
  --2005 version: 
select   name, type from sys.all_objects where is_ms_shipped = 0 and  (type = 'v' or type = 'u') and name <> 'sysdiagrams' order by name 
OPEN tnames_cursor;
DECLARE @tablename varchar(100);
DECLARE @type varchar(5)

-loop through the cursor, pulling off all the column names into your table

FETCH NEXT FROM tnames_cursor INTO @tablename, @type;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN   
      SELECT @tablename = RTRIM(@tablename); 
	  INSERT INTO [dbo].[DataDictionary]
           ([table_qualifier]
           ,[table_owner]
           ,[table_name]
           ,[column_name]
           ,[data_type]
           ,[type_name]
           ,[precision]
           ,[length]
           ,[scale]
           ,[radix]
           ,[nullable]
           ,[remarks]
           ,[column_def]
           ,[sql_data_type]
           ,[sql_datetime_sub]
           ,[char_octet_length]
           ,[ordinal_position]
           ,[is_nullable]
           ,[ss_data_type]) exec sys.sp_columns @tablename
 
   END;
   FETCH NEXT FROM tnames_cursor INTO @tablename, @type;
END;
CLOSE tnames_cursor;
DEALLOCATE tnames_cursor; 






Thanks, 

Liz 


This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, September 21, 2009 12:42 PM
To: Dba-Sqlserver
Subject: [dba-SQLServer] Test for empty columns

My database from hell has at least some columns where the column simply contains no data in any 
record.  I am wondering how I would find and list these columns so that I can delete them from my table.

Would I write a stored procedure to get the columns from the database and then just iterate through 
that recordset building a dynamic query?  Is there already something out there that does this? 
Anybody doing this?

TIA,

-- 
John W. Colby
www.ColbyConsulting.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com