DECLARE @TableName VARCHAR(32) = 'ABCD';
SELECT
	'exec sp_rename ''' +  QUOTENAME( @TableName) + '.'  + QUOTENAME(COLUMN_NAME) +
	''', ''' + QUOTENAME(LOWER(COLUMN_NAME)) + ''', ' + '''COLUMN'';'
FROM
	Information_Schema.[COLUMNS]
WHERE
	Table_Name = @TableName;

SELECT COLUMN_NAME + ' AS ' + LOWER(COLUMN_NAME) + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable'

quote:

---

Originally posted by cjp

Thank you for the suggestions. I have tried sunitabeck's approach and it definitely works - but I have two observations about the output it generates:

1 - If your collation is case-sensitive, you will receive an error. The solution is to type INFORMATION_SCHEMA as all upper case.

2 - The columns have been renamed in lower case but the form has changed subtly but crucially: the output from sp_rename gives this (for example): exec sp_rename '[b9867].[ADMIDATE]', '[admidate]', 'COLUMN'; and testing the eventual renamed table on a create table routine shows that all the columns now have square brackets as part of the actual name; eg [[admidate]]] [varchar](50) NULL,

There is also the puzzle of the third closing square bracket (there for all column names). In a normal Create Table routine from an existing table, the column names are within single square brackets - so I am at a loss here. Testing by querying for admidate generates the error that there is no such field!

Can I modify the sp_rename method to avoid the generation of additional brackets?

Thanks.

Chris

---