Method code to read the number and name of sheet in Excel using SQL script _mssql

Copy Code code as follows:

---Get Table (worksheet) or column (field) listings from a Excel spreadsheet

--Set Variable
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

--Adding a service object
--ACE 12.0 works well for *.xls and *.xlsx, you can also use Jet, but only access *.xls files
EXEC sp_addlinkedserver
@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 current user as login to this link service
EXEC sp_addlinkedsrvlogin
@rmtsrvname = @linkedServerName,
@useself = ' false ',
@locallogin = @suser_sname,
@rmtuser = NULL,
@rmtpassword = null

--Returns the columns in 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