Database

Member 8037172 wrote:

What technique(s) do you prefer?

Declare @tblPlaceholder table(ID int,TableName Nvarchar(20),FieldName Nvarchar(20))
insert into @tblPlaceholder 
	select 1,'spt_values','name' union all select 1,'spt_values','number' union all
	select 2,'spt_monitor','lastrun' union all select 2,'spt_monitor','cpu_busy'
--select * from @tblPlaceholder

Declare @t table(ID int,Query VARCHAR(2000))
Declare @QueryList VARCHAR(2000)
Declare @i int 
set @i = 1

-- Step 1: Build the query and insert the same into a table variable
Insert into @t 
Select ID, Query = ' Select ' + FieldNames + ' from ' + TableName
From
(
Select ID,TableName, FieldNames = Stuff((select ',' + CAST(TableName as Nvarchar(20)) + '.' + CAST(FieldName as Nvarchar(20)) 
			from @tblPlaceholder t2 where t2.ID = t1.ID
			FOR XML PATH('')),1,1,'')
From @tblPlaceholder t1
Group By t1.ID,t1.TableName)X

/* output of step 1
select * from @t

ID	Query
1	 Select spt_values.name,spt_values.number from spt_values
2	 Select spt_monitor.lastrun,spt_monitor.cpu_busy from spt_monitor
*/


-- Step 2 : loop thru the ID and execute the queries
While (@i <= 2) -- since there are two id's.. this can even be configurable as max(id)
Begin
	  SELECT @QueryList = (Select Query from @t where ID = @i)
	  exec(@QueryList)	  
	  set @i  += 1
End

/* Final output

	Output of Select spt_values.name,spt_values.number from spt_values
	
	name	number
	(rpt)	-32768
	YES OR NO	-1
	SYSREMOTELOGINS TYPES	-1
	SYSREMOTELOGINS TYPES (UPDATE)	-1
	
	
	Output of Select spt_monitor.lastrun,spt_monitor.cpu_busy from spt_monitor
	
	lastrun					cpu_busy
	2008-07-0916:46:13.877	10
*/

berba wrote:

Note: I don't want to convert it to varchar.

select cast(getdate() as date)

Mycroft Holmes wrote:

<layer>almost no one uses it. <layer>The normal design is for the application to have a SQL userid/password that is authorised to access the parts of the database required

"Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This dramatically reduces the chances of error or oversight.

For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you use Windows authentication mode, revoking that user’s access takes place automatically when you disable or remove the DBA’s Active Directory account. If you use mixed authentication mode, you not only need to disable the DBA’s Windows account, but you also need to comb through the local user listings on each database server to ensure that no local accounts exist where the DBA may know the password. That’s a lot of work! "