Objects in SQL File Group

I want know which object sit in SQL filegroup.
Do some google and found this script to list objects per filegroup

What SQL job in SQL Server

My x colleague ask me how to query SQL job in SQL Server
Normally I use the below script

use msdb
select name from sysjobs
where enabled=1

Loop through all database and do something

I code this :

declare @database varchar(50)
declare @strSwitchDB nvarchar(100)
declare @count int
declare @maxRow int
SET @count = 1
SELECT @maxRow=max(database_id) FROM sys.databases
WHILE (@count <= @maxRow)
BEGIN
SELECT @database = name FROM sys.databases WHERE database_id = @count
set @strSwitchDB='use '+@database
print 'do something here, processing'
EXEC sp_executesql @strSwitchDB
set @count=@count+1
END

I expected the process will run in all databases in SQL server
example :
use master
do something
use tempdb
do something
use msdb
do something


but it actually no return expected result. The process will run in same database instead run in all database.

example
use master
do something
do something
do something

I actually find another way to do this
if we want to loop through all database and do something,
we actually can write in one line

EXECUTE sp_msforeachdb 'SELECT ''[?]'', count(1) from [?].dbo.sysobjects'

Result
It actually loop through all the database and count number of row in sysobjects table
-------- -----------
[master] 3594
-------- -----------
[tempdb] 67
------- -----------
[model] 47
------ -----------
[msdb] 754

JFYI,The above is for example and not the the actually action I want to perform

Order by "Alias Name"

We have this query:

create table testme11
(id int identity(1,1),
source_name varchar(20),
source_description varchar(20))

insert into testme11 values ('A',NULL)
insert into testme11 values (NULL,'Q')
insert into testme11 values ('E',NULL)
insert into testme11 values (NULL,'C')

select * from testme11
id source_name source_description
----------- -------------------- --------------------
1 A NULL
2 NULL Q
3 E NULL
4 NULL C

(4 row(s) affected)


Are this three queries will return the same result?
A.
SELECT id,ISNULL(source_name,source_description) AS source_name
FROM testme11 src
order by src.source_name

B.
SELECT id,ISNULL(source_name,source_description) AS source_name
FROM testme11 src
order by source_name

C.
SELECT id,ISNULL(source_name,source_description) AS source_name
FROM testme11 src
order by ISNULL(source_name,source_description)

result A:
id source_name
----------- --------------------
2 Q
4 C
1 A
3 E
(4 row(s) affected)

result B:
id source_name
----------- --------------------
1 A
4 C
3 E
2 Q

(4 row(s) affected)


Result C:
id source_name
----------- --------------------
1 A
4 C
3 E
2 Q

(4 row(s) affected)

Query B & C will return same result, while Query A won't return expected result

Check Schedule Job Status

In SQL Job, we can know which schedule job is running from the below step:
Microsoft SQL Server Management Studio -> Server name-> SQL Server Agent-> Job Activity Monitor->Right click -> View Job Activity

You can view which job is running with check on "Status" column

Another way to do this is run this query in Microsoft SQL Server Management Studio->New query

msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

This script will show which job is running now.

Check Last Backup

Recently, T-Mobile and Danger, the microsoft-owned subsidiary that makes the SideKick, has just announced that they've likely lost all user data that was being stored on Microsoft's Servers due to a server failure.
Source : T-MObile Sidekick Disaster: Danger's Servers Crashed, And They Don't Have A Backup

Database Server with important data without backup will kill your company. How you know you already backup your entire important database?

I not remember where I get this script, but basically this script will tell us when is our last full backup of sql database

SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as
LastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
WHERE T2.TYPE='D'
GROUP BY T1.Name
ORDER BY T1.Name

if you want know more then full backup, you can run the below script

SELECT
T1.Name as DatabaseName,
backuptype=case type
when 'D' then 'Database'
when 'I' then 'Differential database'
when 'L' then 'Log'
when 'F' then 'File or filegroup'
when 'G' then 'Differential file'
when 'P' then 'Partial'
when 'Q' then 'Differential partial'
else 'NA'
end,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as
LastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name,T2.Type
ORDER BY T1.Name

reference :
BackupSet
T-MObile Sidekick Disaster: Danger's Servers Crashed, And They Don't Have A Backup

Compare delimiter data from 2 table

Table 1
ID | Country
---------------
1 | French， China，Japan
2 | Malaysia, Singapore

Table 2
ID | Country1
---------------
1 | Japan， China


Result i want :
ID | Country
---------------
1 | French, Malaysia, Singapore


create table test1
(id int,
country varchar(50))
create table test2
(id int,
country varchar(50))
insert into test1 values (1,'French, China,Japan')
insert into test1 values (2,'Malaysia, Singapore')
insert into test2 values (1,'Japan, China')


WITH extract1 (country_Attributes) AS
(
SELECT
CONVERT(XML,''
+ REPLACE(country,',', '')
+ '') AS country_Attributes
FROM test1
union all
SELECT
CONVERT(XML,''
+ REPLACE(country,',', '')
+ '') AS country_Attributes
FROM test2
)
SELECT
country_Attributes.value('/country[1]/Attribute[1]','varchar(25)') AS a,
country_Attributes.value('/country[1]/Attribute[2]','varchar(25)') AS b,
country_Attributes.value('/country[1]/Attribute[3]','varchar(25)') AS c,
country_Attributes.value('/country[1]/Attribute[4]','varchar(25)') AS d
into #temp
FROM extract1;

select country into #temp2 from
(SELECT rtrim(ltrim(colval)) as country
FROM
(SELECT a, b,c,d
FROM #temp) p
UNPIVOT
(ColVal FOR Col IN
(a, b, c,d)
)AS unpvt) tmp
group by country
having count(1)<2

declare @retstr varchar(8000)
select @retstr = COALESCE(@retstr + ',','') + country
from #temp2
print @retstr

drop table #temp
drop table #temp2