Archive for January, 2018

generate a comma seperated list of columns heading

January 20, 2018

alter proc xxx.usp_generate_column_name_string
@database varchar(100),@schema varchar(100), @table varchar(100)

as

declare @str varchar(max) = ‘

select *,”’+@database+”’ as database_name,”’+@schema+”’ as schema_name,”’+@table+”’ as table_name from (
select stuff((select ”,” + name as [text()]
from
(
select c.name from ‘ + @database + ‘.sys.tables a
inner join ‘ + @database + ‘.sys.schemas b on a.schema_id = a.schema_id
inner join ‘ + @database + ‘.sys.columns c on c.object_id= a.object_id
where b.name = ”’+@schema+”’ and a.name =”’+@table+”’) x

for xml path (””)),1,1,””) cols
) a


exec (@str)

go

exec xxx.usp_generate_column_name_string ‘test’ , ‘dbo’,’jl1_tmp’

Advertisements

Defrag Database Files

January 15, 2018

I was prevented from running CHECKDB in one of our largest databases: CheckDB fails with error: The operating system returned error 665

This is due to fragmentation of database files.

I checked the fragmentation using: https://docs.microsoft.com/en-us/sysinternals/downloads/contig

I could not get the 64 bit version to work but the 32 bit version works

Extract the exe to the c drive, not your users folder and open up a command prompt in that directory example c:\contig -a “file path” will tell you how fragmented your file is, in my case 1.7 million fragments c:\contig “file path” will defrag the file, you dont need to be offline to do this. You can check progress by opening up a new command prompt and repeating the command with the -a switch