I was just working on a new SQL job, and part of creating the job was adding a few new tables to our DBA maintenance database to hold data for the job. I created my monitoring queries, and then created new tables to hold that data
One tip - use SELECT...INTO as an easy way to create these types of tables - create your query and then add a one-time INTO clause to create the needed object with all of the appropriate column names, etc.
 |
| https://i.redd.it/1wk7ki3wtet21.jpg |
SELECT DISTINCT SERVERPROPERTY('ServerName') as Instance_Name
, volume_mount_point as Mount_Point
, cast(available_bytes/1024.0/1024.0/1024.0 as decimal(10,2)) as Available_GB
, cast(total_bytes/1024.0/1024.0/1024.0 as decimal(10,2)) as Total_GB
, cast((total_bytes-available_bytes)/1024.0/1024.0/1024.0 as decimal(10,2)) as Used_GB
, cast(100.0*available_bytes/total_bytes as decimal(5,2)) as Percent_Free
, GETDATE() as Date_Stamp
INTO Volume_Disk_Space_Info
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID)
order by volume_mount_point
I thought at this point that everything was set, until I tried to write my next statement...
The dreaded Red Squiggle of doom!
I tried to use an alias to see if Intellisense would detect that - no luck.
Some Google-Fu brought me to the answer on StackOverflow - there is an Intellisense cache that sometimes needs to be refreshed.
The easiest way to refresh the cache is simply a CTRL-SHIFT-R, but there is also a menu selection in SSMS to perform the refresh:
Edit>>Intellisense>>Refresh Local Cache
In my case, once I performed the CTRL-SHIFT-R, the red squiggles disappeared!
 |
| https://memegenerator.net/img/instances/61065657/you-see-its-magic.jpg |
Hope this helps!