Get the schema of a temp table

I wanted to find out what data types SQL Server auditing was using, so I put the auditing file results into a temp table and executed tempdb..sp_help on that temp table to get the schema.

drop table if exists #myTempTable
select top 1000 *
into #myTempTable
FROM sys.fn_get_audit_file ('E:\sqlaudit*.sqlaudit',default,default)
exec tempdb..sp_help '#myTempTable'


This will give you the following results:

Here’s the expanded information on the temp table schema:

Then we can see what kind of data lives in the audit file. It helps you set up a table to hold auditing information making sure you won’t have the wrong data type or too short of a data type. I might not choose the same data type as the audit file, but at least it gives me an idea of what would be the best choice.

Here’s more information about sp_help: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-transact-sql?view=sql-server-ver15

To learn more about auditing, visit https://sqlkitty.com/auditing/

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.