Scenario:
Was working on a project and needed to get list of all the tables from all the databases with columns. Write below script. The script uses cursor to loop through all the databases to get the list of tables with columns. You can filter the databases on which you would like to run the query. I have ignored the system databases such as master, model, tempdb and msdb.
USE master GO --Declare Variables DECLARE @DatabaseName AS VARCHAR(500) --Create Temp Table to Save Results IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results CREATE TABLE #Results ( ServerName VARCHAR(128) ,DatabaseName VARCHAR(128) ,SchemaName VARCHAR(128) ,TableName VARCHAR(128) ,ColumnName VARCHAR(128) ,Data_Type VARCHAR(128) ,Is_Nullable VARCHAR(25) ,Character_Maximum_Length VARCHAR(10) ) DECLARE CUR CURSOR FOR --Filter the Database for which you would like to get all the tables with columns SELECT '[' + NAME + ']' AS DBName FROM sys.databases WHERE NAME NOT IN ( 'master' ,'tempdb' ,'model' ,'msdb' ) OPEN Cur FETCH NEXT FROM Cur INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN --Build dynamic sql for each database DECLARE @SQL VARCHAR(MAX) = NULL SET @SQL = 'Insert into #Results Select @@ServerName, Table_Catalog as DatabaseName, Table_Schema AS TableSchema, Table_Name AS TableName, Column_Name AS ColumnName ,Data_Type ,Is_Nullable ,Character_Maximum_Length From' + @DatabaseName + '.information_Schema.Columns where Table_Name in (Select Table_Name from ' + @DatabaseName + '.information_schema.Tables where table_type=''BASE TABLE'')' EXEC (@SQL) PRINT @SQL FETCH NEXT FROM Cur INTO @DatabaseName END CLOSE Cur DEALLOCATE Cur SELECT * FROM #Results order by DatabaseName,TableName
Mua vé máy bay tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ bao nhiêu tiền
vé máy bay từ hà nội về sài gòn
vé máy bay đi hà nội tết 2020
đặt vé đi nha trang
vé máy bay đi quy nhơn tháng 2
taxi sân bay hà nội nội bài
Combo Phú Quốc