Code for reading the number and name of sheet in Excel using SQL scripts

Copy codeThe Code is as follows:
-- Get table (worksheet) or column (field) listings from an excel spreadsheet

-- Set variables
Declare @ linkedServerName sysname = 'tempexcelspreadsheet'
Declare @ excelFileUrl nvarchar (1000) = 'd: \ text.xlsx'
--/SET

-- Delete the Link Service (if it already exists)
If exists (select null from sys. servers where name = @ linkedServerName) begin
Exec sp_dropserver @ server = @ linkedServerName, @ droplogins = 'droplogins'
End

-- Add a service object
-- ACE 12.0 works well for *. xls and *. xlsx. You can also use Jet, but you can only access *. xls files.
Exec sp_addmediaserver
@ Server = @ linkedServerName,
@ Srvproduct = 'ace 12.0 ',
@ Provider = 'Microsoft. ACE. OLEDB.12.0 ',
@ Datasrc = @ excelFileUrl,
@ Provstr = 'excel 12.0; HDR = yes'

-- Get current user
Declare @ suser_sname nvarchar (256) = suser_sname ()

-- Add the current user as the login Link Service
Exec sp_add1_srvlogin
@ Rmtsrvname = @ brief servername,
@ Useself = 'false ',
@ Locallogin = @ suser_sname,
@ Rmtuser = null,
@ Rmtpassword = null

-- Returns the columns in the sheet and each sheet.
Exec sp_tables_ex @ linkedServerName
Exec sp_columns_ex @ linkedServerName

-- Delete a linked service object

If exists (select null from sys. servers where name = @ linkedServerName) begin
Exec sp_dropserver @ server = @ linkedServerName, @ droplogins = 'droplogins'
End