Does querying fragmented tables in SQL Server Affect the database? On SQL Server 2008, I'm running the following query on a 220GB database to list out the indexes and their levels of fragmentation:

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent, avg_fragment_size_in_pages, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
ORDER BY indexstats.avg_fragmentation_in_percent DESC


It's taken 8 minutes and then I cancelled the query, as it's a production DB (no DEV to test on), on this page someone stated it affects the server and should be run at slow times:

https://serverfault.com/questions/84118/how-long-does-reindexing-take-on-sql-server

I'm just curious how it would impact the server, short of using memory/cpu for the query, is it causing locks on tables I'm not aware of?
The link you posted refers to rebuilding the indexes, which can have a performance impact as they may take indexes offline and they also cause a high amount of I.o when rebuilding. 

As far as your query there will be a slight performance impact from the amount of I.o you are doing but I doubt it will be noticeable. 
In your query --

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)


Specifies LIMITED mode which is the fastest mode and scans the smallest number of pages, as this is the default run level. 

https://msdn.microsoft.com/en-us/library/ms188917.aspx

Paul Randal has a good post that shows the performance impact and how you can monitor if anything is happening on your system when you kick off this job.
http://www.sqlskills.com/blogs/paul/inside-sys-dm_db_index_physical_stats/

One other thing, it does look like you are going to be filtering out heaps in your database based on your join to index_id. I always like to confirm what is happening with heap tables and how many I have when looking at a database from a fragmentation level. 

This query will return the schema name and table name for each heap table that you have...

SELECT SCH.name AS [Schema Name] ,
OBJ.name AS [Table_Name]
FROM sys.indexes INX
JOIN sys.objects OBJ 
ON INX.object_id = OBJ.object_id
JOIN sys.schemas SCH 
ON OBJ.schema_id = SCH.schema_id
WHERE OBJ.is_ms_shipped = 0 --filters SQL objects
AND INX.index_id = 0 --meaning it has none
AND OBJ.type = 'U'; --user table
GO